topic Re: Filter rejects from an inner join reject output in Talend Studio

Filter rejects from an inner join reject output

Anonymous — Sat, 16 Nov 2024 10:15:23 GMT

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

Re: Filter rejects from an inner join reject output

vapukov — Tue, 15 Nov 2016 04:59:11 GMT

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

Re: Filter rejects from an inner join reject output

Anonymous — Wed, 16 Nov 2016 04:51:31 GMT

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.

Re: Filter rejects from an inner join reject output

vapukov — Thu, 17 Nov 2016 04:26:17 GMT

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