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
Helpful Answers
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 🙂
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?
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 🙂
