Identifying correspondances

[GerardPowers]

Dear all,

I am a new Alteryx user and am currently practicing on a client dataset (50k entries). Data is under following format :

Company	Common  ID	Client code
A	1	001
A	2	002
B	2	AAA
B	2	AAB
A	3	003
B	3	AAC
B	4	AAD

As you can see, each company has its own client code format. My goal is to automatically match for the same common ID the company A client code and company B client code, with following rules :

• If for a given client code there is no client code in the other company : show "None"
• If for a given client code there is only 1 client code in the other company : show this code
• If for a given client code there are several matches in the other company : show "Several"

Therefore, the expected output is the following :

Client code	Company A client code	Company B client code
001	001	None
002	002	Several
AAA	002	AAA
AAB	002	AAB
003	003	AAC
AAC	003	AAC
AAD	None	AAD

What would be the alteryx workflow to use in this case ?

Thank you very much in advance for your help,

Best regards 

[BrandonB · Accepted solution]

This was an interesting one because of the way you leverage the client code on the left hand side of the data set that reflects all combinations. Workflow is attached. 

 

 

[GerardPowers]

Thank you very much Brandon, it works well !

I really enjoy the possibility of having all matches listed and separated by a ",", just before your last formula tool. However, I'd like to remove duplicates (ex: for 1 company A client code, I may have "AAA, AAA, AAA, AAA, ABC" and I'd like only to have "AAA, ABC").

How would you proceed ? 

Thanks again

[BrandonB · Accepted solution]

Can you give this one a try? I added in some additional logic that should clear those duplicates before it gets to the formula tool. Workflow is attached. 

[GerardPowers]

Thank you very much, it is perfect !