Need help with strings false differences

Question

Hi, so I'm doing a simple project where I need to compare current year's data with prior year's data and determine whether the information under the "Company" header is new/removed/or match as of current year. However, there are company's where they are really the same but due to minor name difference it is showing up as either "New" or "Removed" when it really should be a match. For example, let's say company name Facebook USA was in the 2021 input file, but in 2022 input file it was changed to Facebook United States. They really are the same but Alteryx will read it as different.

Can someone please offer any potential solution for this? It seems to be a pretty simple fix but I'm new to Alteryx!

Attached is a sample workflow of what I'm trying to achieve.

Sample Workflow.yxmd

binu_acs · Answer

@chokwoo One way of doing this with the combination of find and replace and fuzzy match tool

Sample Workflow (1).yxmd

danilang · Answer

Hi @chokwoo

If you only have these two cases, you can do it a formula tool

Replace(Replace([Company],"United Kingdom","UK"),"United States","USA")

If you have many replacement options, put them in Text Input tool with one column for TextToFind and another for Replacement.  Then use a Find Replace tool, to act on the [Company] field to perform all the replacements

Dan

bkurt · Answer

Hi Chokwoo,

I would use a text to columns functions with a delimiter of space in order to separate company from country.

Then inventarise every variant of the country names there are with pen and paper and then you can replace the variants of country names you don't want with a simple if function.

Later you can add the two strings back together and I would recommend to use the data cleaning tool to remove whitespaces and modify cases into lower case.

Then your join should work.

Good luck!