Four Stars

how to capture rejected records of lookup file

reject.PNG

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.

d_details.PNGe_details.PNG

18 REPLIES
Twelve Stars

Re: how to capture rejected records of lookup file

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.

Rilhia Solutions
Four Stars

Re: how to capture rejected records of lookup file

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.

Twelve Stars

Re: how to capture rejected records of lookup file

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. 

Rilhia Solutions
Twelve Stars

Re: how to capture rejected records of lookup file

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

Rilhia Solutions
Six Stars

Re: how to capture rejected records of lookup file

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

 

 

Four Stars

Re: how to capture rejected records of lookup file

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?
Twelve Stars

Re: how to capture rejected records of lookup file

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.

Rilhia Solutions
Four Stars

Re: how to capture rejected records of lookup file

d_details.PNGe_details.PNG

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?

Six Stars

Re: how to capture rejected records of lookup file

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_2_0, Please correct me if I am wrong.

 

Thanks,

Sachin

 

Twelve Stars

Re: how to capture rejected records of lookup file

Yes @sachinddhake is correct.

Rilhia Solutions
Seven Stars

Re: how to capture rejected records of lookup file

Hi Sachin,

I am also trying to achieve the same here, but the settings suggested doesnt work for me, could you please have a look settings in tMap and let me know what i am missing here ?
here -
1) row3 file is master/main table
2) row4 file is lookup file and is empty currently

so my outptut out1 should have all records from row3 file

Thanks.
Vidyadhar

Twelve Stars TRF
Twelve Stars

Re: how to capture rejected records of lookup file

By definition, "capture rejected inner join" is for inner join and you have a left outer join

TRF
Seven Stars

Re: how to capture rejected records of lookup file

so then, how would i be able to achieve it.

if i set the catch output reject as true, will it help ?

 

i have tried that setting also but didnt achieve the desired result

Twelve Stars TRF
Twelve Stars

Re: how to capture rejected records of lookup file

If you need an inner join, select "inner join" for the lookup table in the tMap.

TRF
Seven Stars

Re: how to capture rejected records of lookup file

Thanks, it worked
Twelve Stars TRF
Twelve Stars

Re: how to capture rejected records of lookup file

Great!
Don't forget to mark the topic as solved.

TRF
Six Stars

Re: how to capture rejected records of lookup file

Hi Vidyadhar

 

Perform 

Join Model  : Inner Join 

out 1 : Inner Join reject : True

 

As u mentioned your Row 4 / lookup is empty right now, you shall get all rejected rows in  Out 1

 

Thanks,

Sachin.

Moderator

Re: how to capture rejected records of lookup file

Hello,

Please stick to one topic so that we can pay individule attention to your issue.

Here is a response for your issue on your another topic:https://community.talend.com/t5/Design-and-Development/Capture-rejected-records-in-tMap/m-p/35072#M9...

Best regards

Sabrina

--
Don't forget to give kudos when a reply is helpful and click Accept the solution when you think you're good with it.