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 current match column.  Here is what I am attempting to use:

COALESCE(Lookup([AccMapping], [AccessorialName], [EdiCode], [Code]), [EdiCode])

So if the Lookup returns a NULL, I want EdiCode to be used.

However, this is generating a parsing error "Unhandled case label: LookupRemoteTableExpression".

I would appreciate any suggestions on how to accomplish this.

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

3 Replies

Replies have been turned off for this discussion
  • 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.