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

Count of unique values

ajayb
6 - Meteoroid

Hello Alteryx Community,

 

Just started using designer and would greatly appreciate any help with this task that I have spent hours with no respite!

 

I am looking to feed the Part # and Plant ID  as inputs in a spreadsheet and get the below two columns as output in the same spreadhseet:

 

1. Unique Part #

2. Count of unique Plant IDs corresponding to the unique part #

 

The below table provides a reference for what I am looking to do with 50,000+ part numbers.

 

Part #Plant ID Part #count
212 213
218 202
216   
218   
20124   
2011   
20    
2011   

 

Thank you in advance for the help!

7 REPLIES 7
MarqueeCrew
20 - Arcturus
20 - Arcturus

@ajayb ,

 

 please try using a SUMMARIZE tool:

 

 group by part #
count distinct (plant I'D)

 

 cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
ajayb
6 - Meteoroid

The solution worked like a charm. Thanks Mark!

I do see that the Count Distinct output is counting the empty cells too. Is there a way to exclude them?

MarqueeCrew
20 - Arcturus
20 - Arcturus

Before going into the summarize:: 

 

iif(isempty(trim(field)),null(),field)

 

 now change the summarize to count distinct non null 

 

cheers,

 

 mark

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
ajayb
6 - Meteoroid

Hi Mark,

 

Pardon my ignorance. Should I be using the formula tool to process the Plant ID column to convert the empty cells to null? I do not see any option at the input of the summarize tool to apply the expression your provided.

ajayb
6 - Meteoroid

Mark,

Used the formula tool and got what I needed. Thanks a ton for the help!

rushabh92
5 - Atom

Pandas nunique() is used to get a count of unique values. To download the CSV file used, Return Type: Integer – Number of pandas unique values in a column.

dave46
5 - Atom

Used it yesterday (Sept 19th). Thanks 🙂

Labels