Need help with strings false differences

[chokwoo]

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.

[Qiu]

@chokwoo 
It is not difficult for us to visually check but will be difficult for Alteryx to identify. 😁

Maybe you can explore the tool of "Fuzzy Match" which might be working for this case.

[chokwoo]

Hmm, I'm not sure if the Fuzzy Match was able to do it for me. I'm pretty much looking for the Alteryx equivalent of going in the excel file and doing a Ctrl+H to find text that says "United States" and replacing it with "USA", and doing that same replace function for "United Kingdom" to replace with "UK". 

[bkurt]

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!

[danilang]

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 

[binuacs]

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