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

[poles1c]

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

[Federica_FF · Accepted solution]

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)

[poles1c]

Perfect thanks!