Capturing non key column on lookup match.

[vrishabhsagar]

Hi,

I have a requirement that I state as follows:

There are two cols in source table : ID1 and DATA

There is a reference table having the following cols : ID1 and ID2.

I have to translate ID1 to ID2 in the source file, IF a coresponding match exists in the reference table between ID1 and ID2. If there is no match then populate ID1 as it is.

Currently I am using a lookup stage, where I connect ID1 col from source link to ID1 in the reference link, and then pull ID2 to the target side column.

Is this correct approach? because I am not getting desired results. All the rows from the source files get populated unto the target. What could be a better soulution?

Thanks!

[sud]

You are doing fine except how are you handling the case when the lookup fails? Then, instead of ID2 you have to go on with ID1. So, in the lookup you would put the lookup failure option as "Continue" and keep ID2 column as nullable and then in a transformer after the lookup stage check if ID2 is null or not and populate accordingly.

[vrishabhsagar]

Okay now I tried this instead, I put a reject link in the lookup and collected rejects in another table. Now I notice that all those rows which do not match the lookup go through the reject link, however for those rows where lookup is success, they are not getting translated, ie. it is populating ID1 as it is.

I am missing something very obvious??

Anyways ill try ur suggestion and post back, Thanks a lot! :D

[sud]

I am missing something very obvious??

Ok, check whether you mapped the columns correctly or not in the output --> mapping tab in the lookup stage.