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

Counting the Duplicates

Marius_S
5 - Atom

Hi everyone! I'm a new Alteryx user and I'm really excited to start exploring this software.

I would like to know if there is a way in Alteryx to count how many times a record has been duplicate.

My goal is not to group by the records and get a total count, but to take into consideration only the first record that appears into my data-set.

Below what is an example of what I'm doing in MS Excel in order to get the desired output:

 

A=countif($A$1:A1,A1)
B 
C 
D 
A 
A 
C 
D 

 

And my aim is to get the following output:

 

 

A1
B1
C1
D1
A2
A3
C2
D2

 

Is there a way to perform this in Alteryx? Any help is much appreciated!

5 REPLIES 5
Kenda
16 - Nebula
16 - Nebula

Hey @Marius_S!

 

Welcome to the community! I'm sure that you'll find this a great place to meet new people and receive lots of help when you are having troubles.

 

For this case, I would use a Sort first and sort by Field1. Then, use a Multi-Row Formula tool with the following expression:

[Row-1:New Field]+1

This should do the trick!

 

marius_s.PNG

bbak
9 - Comet

Adding on to BarnesK's solution:

Add in a record ID first, then the rest of her workflow, and then sort again by the record ID. This will get you the original order of the letters as well as the rolling total of the letter count.

 

Capture2.PNG

Marius_S
5 - Atom

Worked like a charm, many thanks!

champ24
7 - Meteor

Super. I have a similar requirement but need show the duplicate counts as 0. Which means I need to show only 1 for first occurrence and 0's for second third or subsequent occurrences. I can do that by adding an if condition to update the same field like iif [field1]>1 then 0 else 1 endif. But wanted to know if the Multi-row formula tile can handle it.

 

Thanks.

GraemeMyerholtz
5 - Atom

I keep getting a "Type Mismatch in Operator +" Parse Error char(14) in the Multirow Tool.  I am trying to count a V_WString column as the example suggests.  Thoughts on why?  I've tried to convert to a number and still didn't work.

GraemeMyerholtz_0-1638883704863.png

GraemeMyerholtz_1-1638883727386.png

 

 

Labels