Forum Discussion

CDavidOrr's avatar
CDavidOrr
Cloud Apps
08-31-2022
Solved

Using Coalesce with Lookup in a custom column

I need to create a custom column on one of my tables.  This column will be a LOOKUP from another table.  However, if the current row has no match in the remote table, I want to use the value from the...
data models
  • harikm007's avatar
    harikm007
    08-31-2022

    Hi CDavidOrr ,

    Try 'case' statement as below:

    case when Lookup([AccMapping], [AccessorialName], [EdiCode], [Code]) = null
then [EdiCode]
else Lookup([AccMapping], [AccessorialName], [EdiCode], [Code])
end

    -Hari

harikm007's avatar
harikm007
Data Warehouse
08-31-2022

Hi CDavidOrr ,

Try 'case' statement as below:

case when Lookup([AccMapping], [AccessorialName], [EdiCode], [Code]) = null
then [EdiCode]
else Lookup([AccMapping], [AccessorialName], [EdiCode], [Code])
end

-Hari

CDavidOrr's avatar
CDavidOrr
Cloud Apps
08-31-2022

Hi Hari,

Yes, that worked.  Thank you.  Would like to know what it was about the COALESCE that it didn't like though.

  • javierecfpn's avatar
    javierecfpn
    Cloud Apps
    08-31-2022

    Because COALESCE does not handle the use of a built-in function as lookup, COALESCE as far as I know only works with singular values from table columns, so case is needed in this situation.