How to Join Names when formatted differently

[mifrahm]

Hello - I am attaching a mock workflow below. I have been having trouble using the join tool to match data from two excel files. The only "identical" column is name, but because the first file has middle names, hyphens. I end up having a lot of fallout in the left and right join tool. There is at least 10,000 rows of names to go through and I tried using the multi-row formula/different ways to parse data, but then it does not split the names correctly. Can someone please help me with this?

I want the names to be fully capital (easily done using the formula tool). However, I not sure which tools of alteryx to use after. 

[mifrahm]

@BrandonB - hello, do you have any insight on this?

[marcusblackhill]

Hi @mifrahm !

I don't know how the middle name can be important to you, but if you can use just first a last name, I did in attached workflow just a preparation in the first input getting by regex the first name only and to the both inputs I replaced '-' by space to be equal.

Let me know if that help

[mifrahm]

@marcusblackhill  - Hi Marcus. Thanks for the reply and sharing your workflow. As you see in the join tool og the workflow you created Beth for Hooch in file one is dropped on the left side and Beth A For Hooch is dropped on the right side.

Do you know how I would account for people who have three word names or four word names in this case? 

[marcusblackhill · Accepted solution]

@mifrahm 

If you can't know the name patterns in you input, maybe you can solve using fuzzy match tool to find the name similars and with that information, replace your dataset with a unique name for the two sides, but use that carefully, some times the name will be much different and can be out of your join anyway.

Look the attached workflow and tell me if that helps more!

[mifrahm]

Hi @marcusblackhill - pardon my delay in getting back to you. This helps to an extent, but isn't an exact answer. I did some research on fuzzy matching and it looks like more of an art than science. So this is a possible solution although for my current need it is not as "automated" as I would like.