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

Record ID Query

DHB
8 - Asteroid

I have data that looks like this (the actual version has about 2400 rows);

NameCourse
Jamesa
Jamesf
Billm
Billf
Billr
Stevek

 

I'd like to give a record ID to each course starting at 1 for each person.  So in the end I want the data to look like this;

IDNameCourse
1Jamesa
2Jamesf
1Billm
2Billf
3Billr
1Stevek

 

Does anyone know how this can be achieved?

 

Thank you in advance for your help.

 

Best,

 

DHB.

4 REPLIES 4
Bob_Blackey
11 - Bolide

Hi DHB,

 

You can use a Multi_row formula tool.

 

in the config

 

Create a new field, ID  (Int 32)

and the formula would be

 

IF [Name] = [Row-1:Name] Then [Row-1:ID]+1  Else 1 ENDIF

 

It looks and sees if the Name is the same as the previous row, if so it adds one, else starts again at 1.

 

 

P.S. oh yeah, Group By  (see below)... must be five oclock somewhere...

 

Cheers,
Bob

 

MarqueeCrew
20 - Arcturus
20 - Arcturus
You can use the multi-row formula to create a new field, ID

Group By: Name

[Row-1:ID] + 1

Cheers,
mark
Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
DHB
8 - Asteroid

Hi Bob

 

Not sure if I followed your instruction correctly as I got 1,1,2,2,2,3.  Instead of 1,2,1,2,3,1.

 

DHB
8 - Asteroid

This one worked for me.  Thank you so much for your help.

Labels