how to capture rejected records of lookup file

[Anonymous]

Hi, I have two files dept (main) and emp (lookup). There are 2 such records in emp file which are not present in dept file. I want to retrieve these 2 records and the join condition is on dno (department no). Please help if anybody knows the solution to this query.

Below are the sample records of dept and emp respectively in which I need to retrieve the highlighted records from emp.

[Anonymous]

Have you tried not linking your tables in the tMap? This way for every 1 row that comes in on your main it will be multiplied by the number of rows in your lookup table. This way you will get every row (it will be a Cartesian join) and filter the excess data after the join.

[Anonymous]

Cartesian join gives n X n records which I do not require. I need only those values which are not present in dept file. Please provide an alternative solution.

[Anonymous]

Yes, I understand that. This is why I said filter after the join. The reason you need to do this is because the lookup data is "pulled in" by a join. Every row in the main flow will be checked against ONLY corresponding rows in the lookup. You want to bring in rows from your lookup which are not connected.

Another way of doing this is to switch the main with the lookup, if that will suit. OR you can attempt a full outer join by having two tmaps with data source 1 as teh main and 2 as the lookup in one, 2 as the main and 1 as the lookup in the other, then joining and filtering where appropriate.

Given your original description it seemed that the easiest way was to attempt the Cartesian join and the retrospectively filter. Obviously that depends on how many rows this will generate for you though. 

[Anonymous]

I've just reread and think I misunderstood. If you ONLY want the rows that do not join, you need to use the "Catch inner join reject" functionality on the output table of your tMap. You may need to switch your main with your lookup depending on what data you want (see above).

[SachinD]

Hi 

Use emp as ur Main flow and dept as lookup.

Also add one more output in tMap, and Mark Catch lookup inner join reject as True for that new output.

Ur new output should have  records in emp file which are not present in dept file.

and first output should have inner join result.

Thanks,

Sachin

[Anonymous]

What if I need to keep emp as lookup and still retrieve the rejected records. Can it be possible to do it using any other component OR include other component other than tMap?

[Anonymous]

I feel that some of the information is being left out here. Can you give an example of the data going in (both lookup and main) and the data yo require out the other side? There will be a way of achieving this (and I think I have probably touched upon it), but I need to know what the exact requirements are.

[Anonymous]

PFA sample records of dept and emp. Now you can see, the highlighted records in emp are not present in dept. I want to retrieve these records. Is there any way to retrieve these records?

[SachinD]

I think only option is to use emp as ur Main flow and dept as lookup as we don't have right outer join in Talend.

Also add one more output in tMap, and Mark Catch lookup inner join reject as True for that new output.

Ur new output should have  records in emp file which are not present in dept file (highlighted in yellow by you).

and first output should have inner join result.

@rhall, Please correct me if I am wrong.

Thanks,

Sachin