Event logs with multiple rows of data per event

Hello,

While testing the upload of several data sets in Celonis, I have encountered the following issue: we don’t always have one row the per event.

What is the most efficient (and standard) way to write generic scripts to transform an event log that doesn’t have one row per event?

I encounter the following cases:

One row of headers, 2 rows per event:
Headers: Event ID / Timestamp

  • Event 1 / Time started DD.MM.YY
  • Event 1 / Time ended DD.MM.YY
  • Event 2 / Time started DD.MM.YY
  • Event 2 / Time ended DD.MM.YY

Or even the following:
Headers: Event ID / Attribute / Value

  • Event 1 / Event type / Create
  • Event 1 / Timestamp / DD.MM.YY
  • Event 2 / Event type / Closed
  • Event 2 / Timestamp / DD.MM.YY

I have been able to do some things in Excel but as I want to load bigger files (to work on real cases, not only test samples) this becomes an issue. Is there anything we can leverage?

1 Like

Hi there,

that is possible, it totally depends on how you set up the transformation script for these activities. Of course you would have to define the logic for each activity.
For the first example, you would have to set up 2 activity scripts like the following:

SELECT 
Headers.EVENT_ID AS "_CASE_KEY,
"Time started" AS ACTIVITY_EN,
Headers.time_started AS EVENTTIME
FROM Headers

and

SELECT 
Headers.EVENT_ID AS "_CASE_KEY,
"Time ended" AS ACTIVITY_EN,
Headers.time_ended AS EVENTTIME
FROM Headers

For the other example you would set up activities simultaneously.

Best,
Viana