Does Celonis include case sorting to compare one activity to a second activity from the subsequent case?

Hi Stephan,

If I understand your problem correctly, you want to iterate over rows (cases) and calculate a value based on that. Unfortunately, that is not possible in PQL as far as I know. I guess you have two alternatives:

SQL

WITH PRICE_DIFF AS (

    SELECT

        "_CASE_KEY"

, "PRICE_COLUMN"

, < Other Columns >

        , ROW_NUMBER() OVER (PARTITION BY  "_CASE_KEY", < Other Columns > ORDER BY "_CASE_KEY" ASC ) AS SEQ -- Create ascending sequence based on case key 

    FROM "< Table Name >"

)

SELECT

    "PD1"."_CASE_KEY" AS "_CASE_KEY"

    , "PD2"."PRICE_COLUMN" - "PD1"."PRICE_COLUMN" AS "PRICE_DIFFERENCE"

, < Other columns >

FROM PRICE_DIFF AS PD1

LEFT OUTER JOIN PRICE_DIFF AS PD1 ON

    "PD1"."_CASE_KEY" = "PD2"."_CASE_KEY"

    AND "PD1"."SEQ" = "PD2"."SEQ" + 1 -- Compare with next row in sequence based on Case Key

;

Python

Create a script in the machine learning workbench that loads the table, use Python + Pandas to iterate over rows and do the calculation, and return the table back to Celonis. Using the 'Run Machine Learning Script' button, you can make the calculation run within an analysis.

Using Celonis in Python:

PyCelonis: Celonis API Tutorials — pycelonis 1.5.8 documentation

Pandas (Iterating over rows in table/DataFrame): python - How to iterate over rows in a DataFrame in Pandas - Stack Overflow