Forum Discussion

yashsri's avatar
yashsri
New Contributor II
8 years ago

Lookup doesn't return null if value is not found in lookup table. why?

Hi,
I am doing a lookup on a table and matching incoming column with lookup column. If that value is not present lookup did not return that column with null. It just doesn’t return that column at all.

I need to filter the record if that value is not found it lookup table. But not able to do so. Other than joining those tables, do you have any other approach.

pipeline

1 Reply

  • stephenknilans's avatar
    stephenknilans
    Contributor
    8 years ago

    You can use a mapper with the nullsafe option turned on, and specify the fields that you need on the source and target. That should give you a null value. You can also simply replace the source value with a string like:

    ((‘LAST_NAME’ in $)?$LAST_NAME: null)

    This is basically two commands:

    1. (X in Y) which returns true if X is in Y. $ means the variables in this path.
    2. (X ? Y: Z) which means that if X is true then Y else Z