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
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.
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