Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

Normalize vendor/supplier names

andyb
6 - Meteoroid

I have a column, 'Name' that contains variations of multiple company names - eg. 'Apple', 'Apple Inc.', 'APPL' etc. I am trying to run this spreadsheet through a workflow where it returns all the columns already in the worksheet + a new column with the normalised version of the names. Basically, you pick one between 'Apple', Apple Inc.' and 'APPL' and apply it all companies that you know are Apple. 

 

I'm thinking of running a fuzzy search and then using its output as a lookup table to create Find/Replace workflow. 

 

Any thoughts on how this can be done will be much appreciated. 

 

Thank you in advance :)

 

Sample set of columns I am working

 

IDNameAddress1Address2CityStateProvinceCountryZipSectorIDParentIDDateCreatedCreatedByDateModifiedModifiedBy
1Apple300 Madison AveNULLNew YorkNew YorkNULLUnited States1001713NULL56:00.51NULLNULL
2Microsoft Corporation1 Microsoft WayNULLRedmondWashingtonNULLUnited States98052-639915NULL57:01.4158:45.11
3APPLOne Infinite LoopNULLCupertinoCaliforniaNULLUnited States95014-208315NULL04:40.21NULLNULL
4MSFT1 Microsoft WayNULLRedmondWashingtonNULLUnited States98052-639915NULL57:01.4158:45.11
5VMware, Inc.3401 Hillview AveNULLPalo AltoCaliforniaNULLUnited States9430415402:01.41NULLNULL
6Hewlett-Packard Company3000 Hanover StreetNULLPalo AltoCaliforniaNULLUnited StatesNULL15NULL02:54.4131:09.45
7Apple, Inc.One Infinite LoopNULLCupertinoCaliforniaNULLUnited States95014-208315NULL04:40.21NULLNULL
8HP700 East Middlefield RoadNULLMountain ViewCaliforniaNULLUnited States94043-403315NULL05:52.71NULLNULL
9HP Inc700 East Middlefield RoadNULLMountain ViewCaliforniaNULLUnited States94043-403315NULL05:52.71NULLNULL
4 REPLIES 4
Philip
12 - Quasar

One way would be to try fuzzy matching on the company name, then take the ones that didn't match and try fuzzy matching on address. The attached workflow missed matching HP with the full Hewlett-Packard name because the names are so dissimilar and the addresses are not the same, but it caught all Apple and Microsoft records.

 

FuzzyMatchCompanyName.png

PaulRB
8 - Asteroid

This makes some good matches:

Capture.PNG

Results:

IDNameSimilar To
1Apple2
2Microsoft Corporation4
3APPL7
4MSFT2
5VMware, Inc.6
6Hewlett-Packard Company5
7Apple, Inc.3
8HP9
9HP Inc8

 

Not perfect, but with more columns to match on it might get better.

 

Paul

andyb
6 - Meteoroid

This is great, Philip. Thank you for the help! 

andyb
6 - Meteoroid

Thank you PaulRB! Much appreciated

Labels