ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a few hours due to scheduled maintenance starting on Thursday, April 22nd at 5pm MST. Please plan accordingly.

Alteryx Designer Discussions

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

Formula to identify unique values within a column?

laurennewton
6 - Meteoroid

Hello all,

 

I am looking for a solution for the following issue:

 

Currently I have an excel formula that looks at a column labeled "Document Number" and compares one document number to others to see if there are duplicates. If there are duplicates the formula populates the column "Unique Values?" with a 1 and if the document number is not listed more than once than it is  assigned a 0.

 

Is there a way to recreate this formula in Alteryx formula tool?

 

Thanks!

ChrisTX
12 - Quasar

The easiest way may be to use a Summarize tool with the action Count Distinct on your Document Number field.  Then you could use a Formula tool to set a new field value to 0 or 1, based on the value in the Count Distinct column.

 

Also take a look at the Unique tool under the Preparation palette, and Only Unique from the CReW macro pack.

 

Chris

apathetichell
12 - Quasar

@ChrisTXis totally right that the summarize tool/count distinct/count distinct non-null is made for this.

 

1) Group by document number

2) count distinct (or count distinct non-null if you have nulls)

Labels