Ignoring NULL values in LOOKUP transformation.

Hi,

Can you please tell me the way to configure the LOOK UP transformation so that it will ignore all the null values ? I want to configure a Look up component for the column "Col1" as follows

All the NULL values of Col1 should not be considered for look-up process. They should be passed to the downstream component as valid rows.

All NOT NULL values of Col1 should be processed by the Look up component.

If there is no matching value present for any NOT NULL value of Col1 then it should be directed to error output.

Regards,

Gopi

Use a conditional split to direct the not nulls to the lookup(!ISNULL(Col1)). use a union to combine the results from the lookup with the records where Col1=NULL from the other conditional split path. set your error on the lookup to redirect rows to handle the records that have no match.

Frank

|||

Frank,

Thank you for your reply.

But, Is there any way to avoid conditional split ? I would like to configure Look up component itself.

Cheers,

Gopi

|||

If you pass the NULL Col1 records to the lookup, you would need an entry in the lookup table to handle the NULL. otherwise, it will fail the lookup and end up in the error table along with the not NULL Col1 records that failed the lookup.

Frank