community
cancel
Showing results for 
Search instead for 
Did you mean: 

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

Highlighted
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

 

 

Bolide

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