Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

Find unique value from a column with multiple entries and count them

5 - Atom

This is what I have:

 

ActionCompanies
ACompany 1; Company 2 LLC
BCompany 2 inc
CCompany one
DCompany 1 international; Company 3; C2
ECompany 3

 

This is the outcome I want

 

CompaniesNumber of actions
Company 12
Company 22
Company 32
C21

 

Problem 1: Find unique values from the column "companies"

Problem 2: Identify names that were spelled incorrectly/ in different ways, but actually belong to the same company

Alteryx Certified Partner
Alteryx Certified Partner

Firstly, you want to use the text to columns tool to make sure there is a single company in each row. You need to use ; as the delimiter and use 'Split to rows' mode, this will bring your data to look something like...

 

ActionCompanies
ACompany 1
ACompany 2 LLC
BCompany 2 inc
CCompany one
DCompany 1 international
DCompany 3
DC2
ECompany 3

 

To get the count you can then use the summerize tool, grouping by the companies field and also performing a count against the companies field.

 

Now as you mentioned, there are issues with your company details, to resolve this there is a couple of approaches you could take, one automated, one a bit more manual; personally, I would apply a manual method to suppress common extensions like 'inc', 'LLC' and 'international'. This can be done via a mapping table and the 'find and replace' tool.

 

Alternatively, a more automated approach, would be through using the fuzzy matching tool; a good starter blog for this tool is here: https://www.theinformationlab.co.uk/2014/04/02/alteryx-tools-focus-fuzzy-match-make-group-unique/

 

Ben

 

 

12 - Quasar

To identify the same company name spelled differently, check out the Fuzzy Match tool

 

 

After you've matched similar company names, use the Summarize tool to count the actions.

Labels