I have a scenario like this
In source i have multiple values in lookup table i have distinct records
Source table
column1
xyz all a
xyz-all
xyz - all
rxz
rxzab - 1
rxzab - all
In lookup table
Lcolumn1 Lcolumn2 Lcolumn3
xyz 2 1
rxz 3 2
i need to get the ouput like this
Column1 Lcolumn2 Lcolumn3
xyz all a 2 1
xyz-all 2 1
xyz - all 2 1
rxz 3 2
rxzab - 1 3 2
rxzab - all 3 2
Please suggest me regarding this,
Thanks in Advance
Lookup
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It's an inner join, isn't it? Therefore a Join stage would seem apposite. Upstream of that stage, generate the lookup value (a substring of the source key). If the reference data set is sufficiently small, a Lookup stage may be more performant. Return all columns except the key from the right/reference input.
Last edited by ray.wurlod on Mon Nov 20, 2006 6:01 pm, edited 1 time in total.
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.
Hi,
Assuming that every data in the first column of your reference table is going to be having a length of 3, you can accomplsh this using a Lookup stage or a Join stage (depending on the reference data volume).
If using a lookup stage, then create an additional column from your source link containing the first 3 characters (you can use the substring function in the Modify stage for this) and then this new column can be made as the Lookup/Join key with the reference link. To your output you can pass the the original column1 and columns 2 and 3 from the reference.
I believe this should get you what you are loking for.
Regards
The Bird
Assuming that every data in the first column of your reference table is going to be having a length of 3, you can accomplsh this using a Lookup stage or a Join stage (depending on the reference data volume).
If using a lookup stage, then create an additional column from your source link containing the first 3 characters (you can use the substring function in the Modify stage for this) and then this new column can be made as the Lookup/Join key with the reference link. To your output you can pass the the original column1 and columns 2 and 3 from the reference.
I believe this should get you what you are loking for.
Regards
The Bird
You can use the same approach when using the join stage too.thebird wrote: If using a lookup stage, then create an additional column from your source link containing the first 3 characters (you can use the substring function in the Modify stage for this) and then this new column can be made as the Lookup/Join key with the reference link. To your output you can pass the the original column1 and columns 2 and 3 from the reference.
The Bird
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
It IS inner join You just need to do the substring piece in an upstream stage.
Code: Select all
WHERE SUBSTRING(left.key,1,3) = right.key
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.