Hi,
I'm trying to see if there is a way that I can search if the values (Customer IDs) within the group (Group ID) occur in any other group (Group ID). It doesn't matter if the order of the values is different just that the values are the same. This must be exactly the same so even if one or 2 values occur in another group then this is okay or if the values in one group occur in another but the other group has extras then this is also okay. i only need it to flag if groups are the exact same.
An example would be:
Flag if: Dont Flag if: Dont Flag if:
Group 1 Customer 1 Group 1 Customer 1 Group 1 Customer 1
Group 1 Customer 2 Group 1 Customer 2 Group 1 Customer 2
Group 1 Customer 3 Group 1 Customer 3 Group 1 Customer 3
Group 2 Customer 1 Group 2 Customer 1 Group 2 Customer 1
Group 2 Customer 2 Group 2 Customer 3 Group 2 Customer 2
Group 2 Customer 3 Group 2 Customer 3
Group 2 Customer 4
Hopefully this covers in enough detail. Thank you in advance all help is appreciated.
Solved! Go to Solution.
Hi @gmcaleavey
Here's an example:
Data (Groups 1 and 2 would be flagged)
Hi Luke,
Thanks for the reply.
Ive added your workflow to mine and my end there seems to duplicates showing
Group 1 Customer 1 Customer 2
Group 1 Customer 1 Customer 2
Group 1 Customer 1 Customer 2
Whereas yours shows
1 123,456
2 123,456
Have you any idea why?
I can't share my workflow due to data privacy.
Hi @gmcaleavey
Tough to say, seems like maybe your data might require a slightly different grouping. Totally understand data privacy, but you should be able to mock up a few rows of fake data in your actual format and share. That would help figure out what's going on.
Hi Luke,
Thanks for the Help yesterday, I had just finished up for the evening before your last comment sorry for the delay. in my case I have 12001 records outputted by the summarise tool.
My Summarise tool
Group ID Group By
Customer ID Concantenate
Output is the unique number of records e.g.
291 1,3,6
292 1
293 2,1
The unique tool D anchor (on Concat_ customer ID) shows
137 1,2,3
138 1,2,3
The join tool joined in Concat_Customer ID, the L anchor outputs 8447 records and the J anchor 6228
output J anchor
123 1,2
123 1,2
123 1,2
123 1,2
123 1,2
124 1,2
124 1,2
124 1,2
124 1,2
I wondering why there seems to be so many more records/duplicates on the unique ID
Hi @gmcaleavey
Do you have duplicates in your source data? Try filtering on one of the groups and looking at the original data to see if it looks right. Based on what you describe thats the only thing that comes to mind.
Hi All
The duplicates are being created after the join because the D output of the Initial output can itself contain duplicates. If these are joined back to the initial data, you'll get multiple copies of the initial data records(See Multiple container). If you perform a subsequent Unique on the D output and join using the U output of this, you'll remove the duplicate records(Unique container)
In the attached example, I added some extra data to get non-unique items in the D output of the initial Unique tool
Dan
Dan/Luke
Thank you for the help with this, that seems to be it working now.