topic Re: Compare 2 collection and check if values match in Product Forum

Compare 2 collection and check if values match

LorenzoCapocci1 — Mon, 29 Nov 2021 22:46:09 GMT

Hi,
I hope all is well...
I am processing a simple comparison of 2 collections, and merging the result into 1, but I have a problem with empty rows...
basically I would like to keep the empty row if a value is not found in the 2nd column, but I am not able to find a solution... seems simple but for some reason i am unable to apply it
Below I provide the example of 2 screen shot as the current report result and the wanted result....

Are you able to provide an example and provide directions?
thank you very much

Current result

Wanted result

Re: Compare 2 collection and check if values match

devneetmohanty07 — Tue, 30 Nov 2021 02:30:57 GMT

Hi Lorenzo,

If I correctly understand, you are validate that the values for material ID present in SDPI collection also exist in COGNOS collection. If they are available then you need to write both the material ID's and also put the match column value as "Yes" and if they are not available you need to only write the SDPI material value, leaving COGNOS value with Match column value as "No". Your wanted collection in that case should be something like this:

From the screenshot that you have provided only for first two rows the match value is "Yes" and for the rest of the rows it is "No" even though the material ID's: GI3445304, GI3445404 and GI3445604 exists in both Material_ID_SDPI and Material_ID_Cognos.

Can you once confirm if my understanding is correct or is there some logic that I am missing to identify these match values?

Re: Compare 2 collection and check if values match

LorenzoCapocci1 — Tue, 30 Nov 2021 06:54:38 GMT

Hi @devneetmohanty07 ,
ye you are correct, I thought I had this working but I was wrong... basically if the value is not found in the 2nd column, it should just leave a blank cell
thank you

Re: Compare 2 collection and check if values match

devneetmohanty07 — Tue, 30 Nov 2021 07:25:52 GMT

Thanks for clarifying the doubt. I have attached the sample solution flow below:

As you can see in the flow, first I looped within the SDPI collection which had the values as below:

The Cognos collection has the below values:

Within the loop the following actions will take place:

- First, I added a row to a defined collection called as "Final Collection" with the fields: SDPI_MATERIAL_ID (text), COGNOS_MATERIAL_ID (text) and Match? (text)
- Next, I used the 'Collection Contains Value' action to check if the current material id column value for SDPI collection exists in the Cognos collection to return me a flag value called 'Matched'.
- If the value of 'Matched' is True, I will set SDPI_MATERIAL_ID and COGNOS_MATERIAL_ID as the current value of MATERIAL_ID column of SDPI Collection and Match? value as "Yes" otherwise if the value value of 'Matched' is False, I will set SDPI_MATERIAL_ID as the current value of MATERIAL_ID column of SDPI Collection, COGNOS_MATERIAL_ID as blank and Match? value as "No"

Post running the solution, the final collection has the following values:

Hope the above solution explanation helps you out 🙂

Re: Compare 2 collection and check if values match

LorenzoCapocci1 — Tue, 30 Nov 2021 12:22:34 GMT

Hi @devneetmohanty07Thank you once again, but Forgive me, can you show me how you updated the 'Collection Contains Value' action please? sorry
I am having a bad day today.

Re: Compare 2 collection and check if values match

devneetmohanty07 — Tue, 30 Nov 2021 13:54:27 GMT

No issues at all @LorenzoCapocci1, my bad I forgot to attach this one screenshot. Please find the below parameters for the action stage: