Hi All,
I have 2 input streams coming into my job. Both of them have different meta data.
File1 has the following meta data:
(All the 6 fields are integers)
col1
col2
col3
col4
col5
col6
File2 has the following meta data:
( both the fields are integers )
ABC
TXT
now i have to perform a look up ( or merge or join ) where the condition is:
if col1 ( from File1) equals to ABC ( from File2) then output TXT.
( if the lookup matches then I have to output TXT as col1_TXT. just to differentiate with other outputs )
if col2 ( from File1) equals to ABC ( from File2) then output TXT.
( this I have to output as col2_TXT)
if col3 ( from File1) equals to ABC ( from File2) then output TXT.
( this I have to output as col3_TXT)
if col4 ( from File1) equals to ABC ( from File2) then output TXT.
( this I have to output as col4_TXT)
if col5 ( from File1) equals to ABC ( from File2) then output TXT.
( this I have to output as col5_TXT)
if col6 ( from File1) equals to ABC ( from File2) then output TXT.
( this I have to output as col6_TXT)
can anyone please suggest me how do I do it in data stage.
Regards
Pa
Look up on 6 fields
Moderators: chulett, rschirm, roy
-
- Charter Member
- Posts: 822
- Joined: Sat Sep 17, 2005 5:25 pm
- Location: USA
You need to use six different Lookup stages. Run the reject links from each of them through a Funnel stage to accumulate the rejected rows. Be careful with the NULL values in both source and reference.Or you can create two staging temp tables and perform some joins using SQL.
I haven't failed, I've found 10,000 ways that don't work.
Thomas Alva Edison(1847-1931)
Thomas Alva Edison(1847-1931)
-
- Premium Member
- Posts: 263
- Joined: Fri Sep 23, 2005 6:49 am
Look up in 6 fields
can you please tell me why should I Run the reject links from each of them through a Funnel stage to accumulate the rejected rows.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Because there's no other way to accumulate them all. Of course, if you want to report separately from each failed lookup, then you would not require the Funnel stage.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.