Alteryx Designer Desktop Discussions

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

Group Rows of Data by Generating a Unique ID for Rows Based on Characters in a String

8 - Asteroid




I am reading in multiple log files at once and need to create a unique ID for each row to keep information from each log grouped together.


 A sample of what I want my data to look like is below


date                time                status                   status_message                unique_id

5-11-2017       00:00:00         Information          Starting task: blue               1  

5-11-2017       00:05:00         Information          Entering task execution       1

5-11-2017       00:02:00         Information          Starting task: green             2

5-11-2017       00:03:00         Information          Entering task execution       2


We know that data from the next log starts when the key words "Starting task" are present in a row in the status_message column. How do I make sure that each row that contains "Starting task" and it's associated rows have a unique identifier?   


Thank you!






11 - Bolide

Hi @poles1c


you can try with a Multi-Row Formula Tool


Create a new field named unique_id, numeric data type (integer any type)


with the formula


IF startswith([status_message], "Starting task:") then [Row-1:unique_id]+1 ELSE [Row-1:unique_id] ENDIF


The unique ID starts from 1 and increase by +1 everytime a record starts with Starting task:, if not (if it starts with Entering task or whatever) the record takes the same ID of the previous row (row-1)

8 - Asteroid

Perfect thanks!