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 
3 REPLIES
Twelve 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.
Twelve 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
-----------