Get the time between 2 specific rows in a dynamic table (LEAD and LAG Function)

Question

Hi there,

I have the following table:

We got for every part diffrent timestamps with a specific work done there.

I would like to calculate the date between the installation and removal date.

The problem ist, that for one part there can be historical data with multiple removals and installtions (like you see in 'Y'). Here I would like to calculate the difference by taking the installtion data followed by the date of the next removal.

I tried the Lead Function:

DATEDIFF(DD, (Lead ( ('DATE'), ORDER BY ('DATE' DESC), PARTITION BY ('PART', 'WORK'),1)), (DATE) )

The problem here is that this function calculates only the days between the removals.

Do you guys have any idea how I can get the dates between the installation and removals dates?

Thank you and best regards!

neil.whita · Accepted Answer

Hi Jurgen,Assuming your work column is in your activities table (if not, can it be?). I'd suggest using SOURCE - TARGET combined with REMAP_VALUES to help you here:https://docs.celonis.com/en/SOURCE---TARGETThis combined with REMAP_VALUES allows you to be specific about which activities you're interested in (and by exclusion, the activities you're not interested in) and then use this to calculate the difference between only the steps you want.So the PQL could be as follows:Source:SOURCE( ACTIVITY_TABLE.ACTIVITY ,REMAP_VALUES (  ACTIVITY_TABLE.ACTIVITY ,['Activity 1', 'Start'] ,['Activity 2', 'End'] ,NULL ))Target:TARGET( ACTIVITY_TABLE.ACTIVITY ,REMAP_VALUES (  ACTIVITY_TABLE.ACTIVITY ,['Activity 1', 'Start'] ,['Activity 2', 'End'] ,NULL ))Time:HOURS_BETWEEN ( SOURCE ( ACTIVITY_TABLE.ACTIVITY ) , TARGET (  ACTIVITY_TABLE.ACTIVITY ) )Hope this helps in some way

jurgen.gakov · Answer

Hi Jurgen,Assuming your work column is in your activities table (if not, can it be?). I'd suggest using SOURCE - TARGET combined with REMAP_VALUES to help you here:https://docs.celonis.com/en/SOURCE---TARGETThis combined with REMAP_VALUES allows you to be specific about which activities you're interested in (and by exclusion, the activities you're not interested in) and then use this to calculate the difference between only the steps you want.So the PQL could be as follows:Source:SOURCE( ACTIVITY_TABLE.ACTIVITY ,REMAP_VALUES (  ACTIVITY_TABLE.ACTIVITY ,['Activity 1', 'Start'] ,['Activity 2', 'End'] ,NULL ))Target:TARGET( ACTIVITY_TABLE.ACTIVITY ,REMAP_VALUES (  ACTIVITY_TABLE.ACTIVITY ,['Activity 1', 'Start'] ,['Activity 2', 'End'] ,NULL ))Time:HOURS_BETWEEN ( SOURCE ( ACTIVITY_TABLE.ACTIVITY ) , TARGET (  ACTIVITY_TABLE.ACTIVITY ) )Hope this helps in some wayHi Neil, this is a great approach and it also worked! Thanky you so much!

Reply

ANY QUESTION?

MORE CELONIS

CHECK US IN

Reply

Sign up

Login with SSO

Login to the community

Login with SSO

Scanning file for viruses.

This file cannot be downloaded

ANY QUESTION?

MORE CELONIS

CHECK US IN