Count of unique values

[ajayb]

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
21	2		21	3
21	8		20	2
21	6
21	8
20	124
20	11
20
20	11

Thank you in advance for the help!

[MarqueeCrew · Accepted solution]

@ajayb ,

 please try using a SUMMARIZE tool:

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

 cheers,

 mark

[ajayb]

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 · Accepted solution]

Before going into the summarize:: 

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

 now change the summarize to count distinct non null 

cheers,

 mark

[ajayb]

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]

Mark,

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

[rushabh92]

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.