Lookup step returning unexpected values
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.
The idea is the following: The lookup should give back the "Value" column's values in cases where Code1=Code1 and Indic1=Indic2.
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
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
Answers
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)
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)
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