multiple columns to single column lookup

[Jayakannan]

Hi,

An input file has column defintion as Id, StatusCode1, StatusCode2,...StatusCode82 and a table has column definition as StatusCode. If any one of the StatusCode (StatusCode1 to StatusCode82) from input file matches with table's StatusCode then that record has to go to one link otherwise it has to go to another link. The StatusCode in table is dynamic which means there can be 3 status codes now and some more status codes can be added in future.

Input sample data:
123,AB,CD,EF,.....XY
124,BA,DC,EF,.....YX
125,BD,CE,AF,.....XZ

Table data:
CD
GH
RT

From the above first record has to go to link1 since StatusCode2 (CD) present in table also, another 2 records has to go to link2 since none of the StatusCode from these 2 records is present in the table.

[boxtoby]

Hi Kannan,

I would consider pivotting the data so you have it in the following format:

123,AB,StatusCode1
123,CD,StatusCode2
123,EF,StatusCode3
etc.

Now your look up is simple. Send the success records down a link which performs a look up on the Id (123) back to the original data to determine if any of the lookups worked. The third column (StatusCodex) may or may not be requied.

Bob.