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 the number of occurance based on unique ID

kovgyuri00
5 - Atom

Hi Everyone,

 

I have been looking for solition for the below quite a while scrolling through the different topics in the community but havent find anything useful so far, unfortunately.

I have a dataset containing employees's position IDs in which I have several duplications within the position ID field (2 maximum for 1 ID) and I would need to alter them in a way that I will only get unique values at the end. E.g. Counrtney and Louise are both finance analysts so their position ID is the same, still I would need one of those IDs to show that it is a duplicate. so I would need to see "P00000414" as an ID for Courtney and "P00000414_1" for Louise. I only got to the point that I count the records by the ID and separate them from my dataset, but I am struggling to count the occurance of each ID.

I would need something like this as an end result:

 

Position IDOccuranceName
P00000414 Courtney
P000004141Louise
P00003834 Hector
P000038341Luis
P20000683 Manuel
P200006831Richard
P20007267 Ben
P200072671Agnes
P20007385 Melanie
P200073851Brad
P20007609 Peter
P200076091George

 

Can you please help?

5 REPLIES 5
LordNeilLord
15 - Aurora

Hey @kovgyuri00

 

If you have already calculated the occurrence you just need a formula tool to concatenate the new value on like this:

 

If Occurrence = 1 Then [Position ID] +"_"+[Occurrence] Else [Position ID] Endif

In situations like this I tend to go for the Multirow formula approach (example attached)

 

Neil 

kovgyuri00
5 - Atom

Hi Neil,

 

Many thanks for the quick response. Maybe it was not crystal clear from my post, but the part that I am struggling with is the calculation itself, not the concatenation. Do you have any idea regarding that?

Bests,

 

György

BenMoss
ACE Emeritus
ACE Emeritus
Check out the multirow formula tool.

I would use this to create an incrimenal ID within each position.

This can be achieved by:

Adding a multirow formula tool to the canvas.

Lets use this to create a new field ‘SUBID’

Then in the group by choose your position ID field (group by essentially means chunk my data to these groups).

Finally your statement would be something like...

[row-1:SUBID]+1

Ben
kovgyuri00
5 - Atom

Works like wonder. Thank you so much!!

LordNeilLord
15 - Aurora
Hey @kovgyuri00

Did you look at the example I provided for you?

It uses the multirow formula to generate your unique ID

Neil
Labels