Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
SOLVED

Increment row number based on values in another field

Asteroid

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
ELSE [UserColNum] ENDIF

 

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.


Adam

Highlighted
Alteryx Partner

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].

Highlighted

Another option - Use TILE tool with following configuration -

Tile Method: Unique values

Unique Fields: Tick Project SYS ID

Highlighted
Asteroid

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

Highlighted
Meteor

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

Labels