Skip to main content
Question

How to do a Running Last based on table

  • March 31, 2020
  • 1 reply
  • 9 views
.
Level 0

Hello all, I spent quite some time exploring all possible functions but Im unable to solve this.

I need to do a running last based on the table as below. I will then use this to minus off the actual quantity. Ive explored the MOVING_X function, PU_X function and it doesnt seem to give me what I need. Any ideas? Appreciate the inputs!

a869462 55.6 KB

1 reply

V
Level 0

Hello impedance,

Thanks for reaching out! What you are looking for is a combination of LAG() and COALESCE().

Lag() returns the preceding row with a certain offset (in your case 1) based on a defined ordering and partitioning. NULL values are skipped.

Additionally, you need COALESCE() which returns the first argument that is not null. The reason is that LAG() will result in an empty row for each first occurrence of a value in TGT_QUANTITY.

For further details an examples, please refer to the Celonis Cloud Help.

Assuming a sorting by EVENTTIME and a partitioning by Case Key, a solution to your case may look like this:

COALESCE( "_CEL_XXX_ACTIVITIES"."TGT_QUANTITY", LAG ( "_CEL_XXX_ACTIVITIES"." TGT_QUANTITY ", ORDER BY ("_CEL_XXX_ACTIVITIES"."_CASE_KEY", "_CEL_XXX_ACTIVITIES"."EVENTTIME"), PARTITION BY ("_CEL_XXX_ACTIVITIES"."_CASE_KEY")))

I hope this formula and the explanation help solving your problem.

Edit Note: You could also possibly use the ACTIVITY_LAG function, with which you wouldnt need to use the PARTITION BY and ORDER BY as it already orders by case.

Viana

Community Code of ConductAccessibility statement