What is the equivalent of SQL NOT IN in Cascading Pipes?

Question

I have two files with one common field, based on that field value i need to get the second file values.

How do i add the where Condition here?

Is there any other PIPE available for NOT IN use?

File1:

tcno,date,amt
1234,3/10/2016,1000
1234,3/11/2016,400
23456,2/10/2016,1500

File2:

cno,fname,lname,city,phone,mail
1234,first,last,city,1234556,123@123.com

Sample Code:

Pipe pipe1 = new Pipe("custPipe");
Pipe pipe2 = new Pipe("tscnPipe");
Fields cJoinField = new Fields("cno");
Fields tJoinField = new Fields("tcno");
Pipe pipe = new HashJoin(pipe1, cJoinField, pipe2, tJoinField,  new OuterJoin());
//HOW TO ADD WHERE CONDITION i.e. CNO IS NULL FROM SECOND FILE
Fields outFields = new Fields("tcno","tdate", "tamt");

I am expecting the output as first file last line [23456,2/10/2016,1500]

Answer 1

Based on the comment in the code:

//HOW TO ADD WHERE CONDITION i.e. CNO IS NULL FROM SECOND FILE

Try using FilterNull.

Add the following line to you code after HashJoin step:

FilterNull filterNull = new FilterNull();
pipe = new Each( pipe, cJoinField, filterNull );

Something like:

Pipe pipe1 = new Pipe("custPipe");
Pipe pipe2 = new Pipe("tscnPipe");
Fields cJoinField = new Fields("cno");
Fields tJoinField = new Fields("tcno");
Pipe pipe = new HashJoin(pipe1, cJoinField, pipe2, tJoinField,  new OuterJoin());

// Filter out those tuples which has cno as null
FilterNull filterNull = new FilterNull();
pipe = new Each( pipe, cJoinField, filterNull );

Fields outFields = new Fields("tcno","tdate", "tamt");