Filter rejects from an inner join reject output

One Star

Filter rejects from an inner join reject output

Hi,
I have a problem, which is as follows:
Table a:
id  name  produce_id  cost  date
1   Tom     a1         3.50    9/26/2016
2   Jerry     b1         4.00    9/26/2016
Table b:
id     produce   start_dt      end_dt   
a1   apple    7/1/2016    9/30/2016
a1   apple   10/1/2016  12/31/2016
I'd like to join table a and table b using a.produce_id = b.id and a.date between b.start_dt and b.end_dt.
The result should yield 1 single row in the matched output stream, and the reject output should only capture 1 row (row 2 from table a, not the inner join "all match" reject based on filter). I want to capture reject rows from table 1.
When I use, tMap and do an inner join with All Match with join keys a.produce_id = b.id and use an out file for capturing matched rows and another file to capture unmatched rows, I get 2 rows in the unmatched (reject) output as it is capturing both the unmatched row and the inner join rejects of the filter.
I'd appreciate if someone could show a sample of the tmap join/filter expression, output (with any expression), and reject output (settings/expressions).
Best,
Shaun 
Highlighted
Thirteen Stars

Re: Filter rejects from an inner join reject output

It not cover all 100% of possible cases, and it catch as rejected from Main flow (not from lookup)
so in Your example Table1 - main, Table2 - lookup:

this case not cover date conditions (need more tests) - You can easy add filter to the output flow and it will reject wrong records, but how include them to the rejected - need more investigate
     
-----------
One Star

Re: Filter rejects from an inner join reject output

Thanks Vapukov for the quick response. I have tried that and was able to capture join rejects.
My issue is with date filter on the joined results that needs to be filtered.
Table a:
id  name  produce_id  cost  date
1   Tom     a1         3.50    9/26/2016
2   Jerry     b1         4.00    9/26/2016
3   Tom     a1         4.00    6/26/2016
Table b:
id     produce   start_dt      end_dt   
a1   apple    7/1/2016    9/30/2016
a1   apple   10/1/2016  12/31/2016
In the above example:
Only the first row in Table a should go through and I should be able to capture the second and third rows as rejects. I am having difficulty in capturing the 3rd row where the join key matches but not the date between.
Thirteen Stars

Re: Filter rejects from an inner join reject output

not ready to warrant for 100% (task hypothetical,but not with all possible)
in additional to the JOIN, add filters, filter conditions same:
TalendDate.compareDate(row2.start_dt,row1.date,"yyyy-MM-dd")<0&&TalendDate.compareDate(row2.end_dt,row1.date,"yyyy-MM-dd")>=0


and reload lookup file for each iteration - this I not sure for 100% how it will work with LOAD ONECE, so I just go by 100% correct way
filter - reject from lookup file records not matched by filter conditions for current record
      
Talend Date functions not understand '9/26/2016' with pattern 'MM/dd/yyyy', You must handle leading 0 for proper work test examples
-----------

Tutorial

Introduction to Talend Open Studio for Data Integration.

Definitive Guide to Data Integration

Practical steps to developing your data integration strategy.

Definitive Guide to Data Quality

Create systems and workflow to manage clean data ingestion and data transformation.