Help with Lookup Logic

[mac4rfree85]

Hi Guys,

My lookup table has 15 columns. My source table also have those 15 columns.

I need to do the lookup only when the column is having value instead of Null Values.

For an example:
Lookup table:

Code: Select all

1  2  3    4    5    6    7    8    9    10   Key
A  B  NULL NULL NULL NULL NULL NULL NULL NULL  1
A  B  NULL Y    NULL NULL NULL NULL NULL NULL  2
C  A  NULL D    E    NULL NULL NULL NULL NULL  3
E  R  A    T    NULL NULL NULL NULL NULL NULL  4

When my source table is having values A and B for Col1 and Col2, i should consider only the first two Column as the lookup and retrieve the key 1.
If my source table is having values A and B for Col1 and Col2 and Y as col4, then i should consider the three columns col1,col2 and col4 and retrieve the key 2.
If my source table have C and A as Col1 and col2, then i need to consider col1, col2, col4 and col5 for lookup and retrieve the key 3.

I hope i made myself clear. Could you please help me with my design.

Cheers!!!!!

[chulett]

Multiple lookup stages, one for each of the combinations listed.

[cipsy]

I think you can use, user defined sql in source stage.

[paultechm]

Concatenate columns 1 to 10 in both source and lookup then perform the lookup based on the concatenated column

[SURA]

Use full outer join.

1. Nulls can be replaced with dummy values to know that it is null
2. Use a TFM and apply whatever the condition you need and get the output