Look up based on two conditions

[Das]

Hi,

I have an issue as follows.

I have 2 source[1 flat file, 1 table].I need to look up the for data coming from the flat file to the table based on the following conditions:
If the field1 in the flat file is equal to Col2 or Col3 of the table .i need to pass the curresponding Col1 [the key column of the table]

Provide me a solution.

[kcbland]

Two lookups, use the results of one else if none then use the other.

[Das]

Two lookups, use the results of one else if none then use the other. ...

Thanks,
I have used two hashed file lookups,And use the following logic in output column of transformer :
If not(IsNull(DSLink86.Col1))
then
Unit_Out.Col1
Else
if not(IsNull(DSLink89.Col1)) then DSLink89.Col1
else @NULL
DSLink86.Col1,DSLink89.Col1 are input links from lookups populating based on the first condition and second condition

But the matched data is not populating

[kcbland]

Shouldn't

Code: Select all

If not(IsNull(DSLink86.Col1)) 
then 
Unit_Out.Col1 
Else 
if not(IsNull(DSLink89.Col1)) then DSLink89.Col1 
else @NULL 

be

Code: Select all

If not(IsNull(DSLink86.Col1)) 
then 
DSLink86.Col1 
Else 
if not(IsNull(DSLink89.Col1)) then DSLink89.Col1 
else @NULL 

[Das]

Shouldn't

Code: Select all

If not(IsNull(DSLink86.Col1)) 
then 
Unit_Out.Col1 
Else 
if not(IsNull(DSLink89.Col1)) then DSLink89.Col1 
else @NULL 

be

[code ...

I am not geeting you exatly..Please expalin

[Das]

Thanks,
I have used two hashed file lookups,And use the following logic in output column of transformer :
If not(IsNull(DSLink86.Col1))
then
Unit_Out.Col1
Else
if not(IsNull(DSLink89.Col1)) then DSLink89.Col1
else @NULL
DSLink86.Col1,DSLink89.Col1 are input links from lookups populating based on the first condition and second condition

But the matched data is not populating and getting the following warning
also

DataStage Job 657 Phantom 11508
Program "DSD.StageRun": Line 544, Attempted WRITE with NULL record ID
DataStage Phantom Finished

[chulett]

The warning means you are attempting to write a hashed file record with NULL key values. You need to ensure that does not happen, either by supplying a default value when null or using a constraint to filter them out.

[kcbland]

Look again at my previous reply. You have the wrong link name.

[rogue]

I have 2 source[1 flat file, 1 table].I need to look up the for data coming from the flat file to the table based on the following conditions:
If the field1 in the flat file is equal to Col2 or Col3 of the table .i need to pass the curresponding Col1 [the key column of the table]

What about normalising the lookup before comparing?

Take the two columns to compare and make only one key column of them.


Best regards
Andreas

[rafik2k]

What are the datatypes of those columns,
If these are varchar then some time don't match due to trailing blank space.
In that case use trim(field1) function in Key Expression.