community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

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

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

Highlighted
Asteroid

Hi,

 

 

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!

 

Courtney

 

 

 

Alteryx Partner

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)

Asteroid

Perfect thanks!

Labels