Lookup step returning unexpected values

Richard Illyes
Richard Illyes Member
edited December 2023 in General


Hello.

I am currently using version 2.8.8.27.

I've discovered recently that in one of my workflows, the Lookup step is not returning the expected results in all cases. I have quickly set up a test scenario I have attached below.

Lookup__Values_Test.dmxd

The idea is the following: The lookup should give back the "Value" column's values in cases where Code1=Code1 and Indic1=Indic2.

image.png

In the attached case, in my understanding the expected value would be "DEFAULT" for row 7, but I get N/A instead.

There is also another lookup column which is set up to show if a record where Code1=Code1 and Indic1=Indic2 exists at all, but that returns True, even if there is no "ID13" for Indic2

image.png

Can you please take a look and see if this is intended, and if so, advise on how I might be able to achieve the desired result?

Thanks in advance.

Best,

Richard

Tagged:

Answers

  • Josh Boxer
    Josh Boxer Administrator
    edited January 2023

    Hi Richard

    Before your Lookup, add a transform that concatenates Code1 and Indic1 columns (replace code1=code1 with this new column). Make the same change for your Exists lookup definition

    (I also changed 'Sorted group values' Lookup type to First as this was clearer)

    image.png
    image.png


  • Richard Illyes
    Richard Illyes Member

    Hi Josh,

    Thanks for your reply.

    I can confirm that getting a default value where expected does work with the option "First value" but if you ask for "Sorted values", in case of no match found it returns a Null (or rather, an empty list, as "sorted values" as far as I know returns a comma separated list) In the below example, the first 9 rows are such examples (with or without the suggested concat column added)

    image.png

    Here in this specific case, later on in the workflow I aim to make some transformations based on this sorted values lookup, for which the base "Value" column does contain valid empty fields.

    Let me rephrase my earlier question a bit: Is this the expected behavior of the Sorted Values lookup type to return an empty list instead of the default value when no matches are found?

    I believe working around this would be possible by implementing a Group step for Code1 and Indic2 and an aggregate the values of the Value column with "sorted group values" and then using a lookup with "First value", but this requires some overhaul which I would like to avoid.

    Thanks.

    Best regards,

    Richard

Categories