Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Increment row number based on values in another field

11 - Bolide

I have a data source that has project numbers and then user names associated with those projects. I want to set a number field starting with one for the first project and increment up to 2 when I get to the next Project number in the list. 


I am trying to do a multi-row formula that looks something like this but can't get it to work (UserColNum was populated with 1 in all rows to start)


IF [Project SYS ID] != [Row-1:Project SYS ID] THEN [UserColNum] + 1


It is only counting up to 2 for the first row where the Project Sys ID is different but the next row stays at 1. How can I get the next rows to be 2 and then up to 3 on the next Project system ID number? 


I am going to eventually use this number to transpose the field so all of the users will end up as new columns across the top for each Project. 


Thank you for the help.


11 - Bolide

HI @aehrenwo


I think you should lookup at the Row-1 UserColumn in order to set the right values:


if [Project SYS ID] != [Row-1:Project SYS ID] then [Row-1:UserColNum]+1  else [Row-1:UserColNum] endif


Or you can take a different way using a summarize group by [Project SYS ID] to get a list of unique ID values, add a RecordID tool after it and then joining back the flow to the input using as match field [Project SYS ID].

10 - Fireball

Another option - Use TILE tool with following configuration -

Tile Method: Unique values

Unique Fields: Tick Project SYS ID

11 - Bolide

Once I figured out the parameters. The Tile Tool worked perfectly. 

8 - Asteroid

Thanks the tile function works great to de-identify a customer ID/Patient ID.