Match Two Columns with same content, but reordered (Eg. Match a+b+c with b+a+c)

[dannyxu]

Hi Alteryx Community

I have a question which is a more like a how-to or workflow improvement type of question. I looked through few postings about Fuzzy Match, which does not really apply to this particular case. Hence reaching out to the Alteryx community.

Background:

I have a workflow comparing two columns. The reference column is a combination of maximum 4 parts at the moment like following examples, with a possibility of increasing to 5 parts.

a + b

a + b + c

a + b + c + d

Example:

7000

8000+ BK123

7000+7300+9970

BEF06+7300+BK123

and I am trying to use this to validate incoming column and report on the differences.

Problem

The problem and the difficulties are that the column in the incoming file could be in random orders as follow.

b + a

b + a + c

a + c + b + d

c + a + b + d + e

Attempted Solution

My 1st solution Attempt is to broke the incoming column into columns (column a, column b, columns c etc.), and then separated them into different combination scenarios, (2 Combinations, 3, 4 and more than 4)

• For 2 combinations,  a + b, I switched new the incoming file to a + b and b + a to join back to the source file a + b to see if any matches.
• For 3 combinations,  a + b + c, I listed out all combinations ,  a+b+c, a+c+b, b+c+a, b+a+c, c+a+b, c+b+a
• However starting from 4 combinations or more this is where it is getting headache with minimum of 24 combinations.

My question is:

• Does Alteryx have any good features to handle this type of matches and report on exceptions?
• Would  Fuzzy Match do these type of match?
• Has anyone done a similar workflow before?
•  

Attached is a sample file with 2 sheets, I am trying to match the Incoming to the Reference Sheet.

Thank you for your time and effort on looking into this.

[JoeS · Accepted solution]

Hi @dannyxu

I have built a workflow that splits the fields out and then sorts the data before joining it to the reference:

If you need to retain the original order, you can join back again on the Record ID. I have attached the workflow as well.

[dannyxu]

Thank you very much for the quick response and the spot-on solution, Joe

This is a much better approach to solve the problem. Re-ordering the parts to the correct order. I am sure lots of Analysts will find this solution very useful and can be applied to many use cases.

Again much appreciated! 

[JoeS]

You're welcome. I am glad it wasn't too complicated of a solution in the end.