Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Alteryx is here to help you solve your biggest data challenges. Read about the new Virtual Solution Center here.

Comparing Two list to see the differences, using Fuzzy Match?

Highlighted
5 - Atom

Hi All,

 

I was wondering if anyone can help me with an Alteryx workflow to compare one list of names to another. Unfortunately I cannot share any of the data sets.

 

What we want to do is compare one list with another to see the differences of the two, ie what is missing on one list that is on the other and what is there on one that isn't on the other.

 

I looked at using Fuzzy match for this as the names are not exactly the same and have a few differences. I have used a find replace tool and lookup tables to make the names that are very different the same, this could of been due to a name change. please see screenshot below of the workflow that I have started:

 

ZacBarnes_0-1575649807026.png

What I have tried to do is I cleansed and sorted the data and then used the unique tool to only show one of the names and not have them repeating. Then as said before I have used a Find Replace tool to change some of the names to how they should be. I have then used a formula tool to add an ID onto the end of each name, either 1 or 2. The reason behind this was to be able to see if the fuzzy match was working and not just joining to itself (which it was). I didn't origninally mind about not doing this I just thought it might fix the problem. What am I doing wrong please?

 

I then tried to join this back together by record position so the lists would sit next to each other, also doing a right outer join to capture all the results. I then did the fuzzy match and it was comparing with itself and for example in one list next to another:

 

    John   1       John 1. 

What I wanted to see was:

    John 1        John 2

 

Ideally i would like this to work by have 2 separate list clearly distinguished which is which and comparing the names next to each other, and if one is missing I would want the record next to it to be empty/null or whatever is possible.

 

I am very new to Fuzzy matching and understand this is down to user error, please could someone help me.

 

Thank you,

 

Zac

Highlighted
Alteryx
Alteryx

Hey @ZacBarnes,

 

 

I then tried to join this back together by record position so the lists would sit next to each other, also doing a right outer join to capture all the results. I then did the fuzzy match and it was comparing with itself and for example in one list next to another:

 

    John   1       John 1. 

What I wanted to see was:

    John 1        John 2

 

Regarding this portion, I think what might do the trick is a make groups tool after the Fuzzy match which is shown in the One Tool Example in Alteryx toward the bottom:

 

So effectively you can "Make Groups" after you do your fuzzy match, and then you can do a find replace after that

PeterA1_0-1575652884556.png

 

If I think of anything else I will come back on this comment. Let me know if this answers your question.

 

Happy Friday!

 

Best,

Peter

 

Highlighted
Alteryx Certified Partner

Hi @ZacBarnes ,

 

The only thing you need to do is before your fuzzy match tool where you are joining the table and you should unite it. Fuzzy match works looking for similarities in the same column and to reference where the data is coming from, you can use a column showing the table origin, for instance.

 

fmvizcaino_0-1575655912082.png

 

Take a look at my attached example and let me know if that makes sense.

Best,

Fernando Vizcaino

Highlighted
5 - Atom

Hi Peter,

 

Thanks for the help, I'll have another look and see if I can solve the issue trying this method.

Thanks,

 

Zac

5 - Atom

Hi fmvizcaino,

 

Thanks for the tip, that's very helpful, especially for someone who is so new to Fuzzy matching, i'll try what you both suggested and see if I can fiddle around to get it to work, thanks for taking the time out to get back to me.

 

Thanks,


Zac

Labels