Hi adi,
There could be more than one logic to do this same thing.
I have attached the files for the overall diagram and the logic of the flow is written below.
If you prefer to do it on your own then you can just go through the logic part in the write-up below.
1.jpg: This contains the complete process diagram.
I've tried to manage the diagram in a single screenshot hence looks clumsy
.
2.jpg: Create an empty OutputCollection with the desired columns of SrNo and Phone Number.
3.jpg: Append Collections 1, 2 and 3 to this new OutputCollection.
In the Output parameter give the name as OutputCollection for all 3 of the Action stages.
4.jpg: After appending all 3 collections it would look like this (based on the data you have posted for each of the 3 Input collections).
5.jpg: Design another collection this time with 3 additional columns as shown. This collection is the one that will contain your Final output
6.jpg: Use a Loop stage to loop through the items in the combined OutputCollection.
In the running loop, check if the value from the OutputCollection is present in the FinalCollection.
The Output parameter for this stage is 'Found' data item.
Here we are trying to remove duplicates as well as update 'Yes' and 'No' in one single loop.
If Found is True then it means the Phone Number is already present so no need to add again. Point it to the Loop End to get the next item.
If Not found then add a new row to the Final Collection using the 'Add Row' action of Collection VBO. Only after adding a new row we will be able to add data to it.
7.jpg: Add a Multi Calc stage which will perform multiple operations at once.
-Add phone number from Output Collection to FinalCollection
-A counter variable is maintained to insert into the Sr.No column of FinalCollection.
Initial value of counter variable is 1
-Increment the SrNoCounter variable so that next time it inserts the next SrNo in the Final Collection
8.jpg: There are 3 action stages to check if the PhoneNumber from the OutputCollection (that we are looping) is present in which of the 3 InputCollections (coll1, coll2, coll3). The output parameter of these 3 Action stages are 3 flags - FoundinColl1, FoundinColl2 and FoundinColl3
9.jpg: If the FoundColl flag variable is True then write 'Yes' to the respective column of the FinalCollection.
If False then write 'No'.
You need to do this thrice, one for each InputCollection.
10.jpg: Once all three collections are compared then the flow is pointed to Loop End so that the next item from the OutputCollection is picked up.
Once the loop is over it shows the final result as this.
You might need to check if it handles scenarios like Empty collection, etc.
Run with various inputs and let me know if there is any problem.
