Flagging if the values within a group occurs exactly the same in another group.

[gmcaleavey]

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.

[Luke_C]

Hi @gmcaleavey 

Here's an example:

Data (Groups 1 and 2 would be flagged)

1. Sort by group/customer
2. Concatenate customers into one record
3. Flag duplicate customer concatenations
4. Join back to flag all groups

[gmcaleavey]

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.

[Luke_C]

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.

[gmcaleavey]

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 

[Luke_C]

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.

[danilang · Accepted solution]

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    

[gmcaleavey]

Dan/Luke

Thank you for the help with this, that seems to be it working now.