OCPM - Merging 2 eventlogs for TPT between first and last activities.

Hi marcus.malbm,

Thanks for sharing the exact lines of code. I see that you have not created extended columns in your eventlogs, which is likely the blocker here.

The CALC_THROUGHPUT function needs so called event labels when you are calculating TPT between two different eventlogs. Essentially, these event labels are extended (string) columns. 

Before that, it is important to analyze the object table linking your two event logs. That is necessary for the 1st step, i.e., creating virtual event logs so that the PQL engine has a combined view of both of your eventlogs.

REGISTER el_custom_PurchaseOrder AS CREATE_EVENTLOG (
    o_custom_Object  -- Replace with your linking object table
    INCLUDE [ e_custom_InboundStart ]
);

REGISTER el_custom_Outbound AS CREATE_EVENTLOG (
    o_custom_Object  -- Replace with your linking object table
    INCLUDE [ e_custom_OutboundMarkedAsLoaded ]
);

Then, you’d create the event labels mentioned earlier.

EXTEND el_custom_PurchaseOrder WITH sourceLabel AS 
    CASE WHEN el_custom_PurchaseOrder.ACTIVITY = 'e_custom_InboundStart' THEN 'InboundStart' ELSE NULL END;

EXTEND el_custom_Outbound WITH targetLabel AS 
    CASE WHEN el_custom_Outbound.ACTIVITY = 'e_custom_OutboundMarkedAsLoaded' THEN 'OutboundMarkedAsLoaded' ELSE NULL END;

Finally, you can use the CALC_THROUGHPUT function that is leveraging the virtual eventlogs and event labels you defined above.

AVG(
    CALC_THROUGHPUT(
        o_custom_Object,  -- Replace with your linking object table
        FIRST_OCCURRENCE['InboundStart'] TO LAST_OCCURRENCE['OutboundMarkedAsLoaded'],
        HOURS
        LABELS_FROM(
            el_custom_PurchaseOrder.sourceLabel,
            el_custom_Outbound.targetLabel
        )
    )
)

Please let me know how you progress with these suggestions.

Best,

Martina

Thank you for your time and effort Medina!
Just for clarification: is this three separate snippets or i can write them in one?
Is anything here part of the backend or it’s something I can do in when i create it in “KPI”?

I don’t want to use the forum as a gpt or google and would love to know if you can recommend anything from the academy?

Once again: thank you!

EDIT: I just understood that part 1 & 2 is backend!

Hi Marcus,

The intention is that you test this in a single query within a view. All three snippets in one single PQL query. Snippet 1 + Snippet 2 + Snippet 3, just copy paste one after the other.

When you use REGISTER, you are essentially creating a virtual table. Therefore, it cannot be saved in the knowledge model (I assume that this is the backend you are referring to). 

Based on your last comment, I take that you also want to save the TPT calculation into a KPI, right? The solution I gave you earlier would not work because of the nature of virtual tables. However, you could create the event log in the knowledge model itself through the Event Log functionality (https://docs.celonis.com/en/eventlogs-oc.html).

My suggestion remains that you first try it out with virtual tables in a view. Once you confirm (through testing) that the code works, you can try recreating the eventlog with the Event Logs functionality in the knowledge model.

Best,

Martina

To your second question about Academy courses (not wanting to abuse this community page).

1. This is what this community forum is for, don’t worry.
2. I assume that you have already been looking at some academy courses to understand PQL better, these courses are great to give you a good understanding of PQL and the most common challenges but don’t necessarily tackle specific questions like yours. → My best suggestion is therefore that you rely on the documentation (in most cases, the PQL documentation) and your AI model of choice that can help you summarize and simplify the relevant parts of the documentation.

Best,

Martina