count the number of occurance based on unique ID

[kovgyuri00]

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 ID	Occurance	Name
P00000414		Courtney
P00000414	1	Louise
P00003834		Hector
P00003834	1	Luis
P20000683		Manuel
P20000683	1	Richard
P20007267		Ben
P20007267	1	Agnes
P20007385		Melanie
P20007385	1	Brad
P20007609		Peter
P20007609	1	George

Can you please help?

[LordNeilLord]

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]

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

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]

Works like wonder. Thank you so much!!

[LordNeilLord]

Hey @kovgyuri00

Did you look at the example I provided for you?

It uses the multirow formula to generate your unique ID

Neil