Hi all,
So I have a dataset with business names separated by region. For example, ABC - UK, ABC - Romania, DEF - US, DEF Inc. I also have a list of standardized names, for example ABC, DEF. How can I use that list to look through the field values and change them to the standardized names?
Hi,
It looks like the find and replace tool will be a place to go. This tool allows us to configure to search fo any part of the field in order to make a match:
I would also suggest appending this new column just to make sure that you can easily check if this is working correctly:
If you want to read more about this tool please see those two links below:
https://help.alteryx.com/current/designer/find-replace-tool
Good luck!
@Emil_Kos
So fast😁
I would need something more complex. The names aren't as cut and dry as the example ones given
Hi @ryanmor24,
Can you show us a few examples of a scenario where find and replace will not work?
We need a little bit more details in order to properly help you.
🤔
1. I would use a Fuzzy Match tool
2. Configure it to use the Merge option and use a source ID field from an Excel table where you have the standardized names
2.1 To do this you have to union your data with the standardized source data (the Excel table with standardized names)
2.2 Use a formula tool to identify the source table (Excel table with standardized names) and your data. This can be done before or after joining the tables (using the union tool). I'm going to explain by doing this before
a. Create a field in both files named "source". In your data, under the newly created "source" field add the expression "Data". In your Standardized names file, under the newly created "source" field add the expression "Standard"
b. Union both files
2.3 In the fuzzy match configuration window, under Source ID field, choose your Standard names in your Standardized names file as the source.
2.4 In the fuzzy match configuration window, under Record ID field, choose your Business name by region field in your data file.
2.5 In the fuzzy match configuration window, under Match fields, specify the field name you need to have standardized/renamed. In this case it is your business name by region field as well. Under Match Style you can select "Name".
***Use the "Match Threshold" up and down arrows to specify how close of a match you want. I think it's set at 80% by default (you can play with this until you get your desired result)***
3. Run the workflow. It will output 2 columns/fields, one with the original Business name by region field and one with the Standardized Business name by region.
4. You can use the output in Step 3 as a mapping for your original data using a Find Replace tool.
This will automate and standardize your fields should new business names by region show up in the future.
Here is a link to an article in the Alteryx community to help with setting this up until Step 3 above.
Prepare two inputs for Fuzzy Match Merge Mode
Enjoy!