DSXchange

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

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.

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

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.

You can use the same approach when using the join stage too.

The Bird

Thanks Ray and the bird

Its not inner join and length is not fixed in source table and lookup table.

It IS inner join You just need to do the substring piece in an upstream stage.