Need help with PQL filtering

try this : 

PU_LAST( OBJECT ,  USER , USER NOT IN (PU_LAST( OBJECT, USER,USER NOT IN (‘AUTOMATED’) ))  ,  ’AUTOMATED’).

Hi ​@bhargav.bhima , I’m guessing that you would also need some kind of PU_COUNT function within there to know what the last non-automatic user was that only created one log, right? Because if you would only look for the last non-automatic user it would be John. So if yes, I would try to do it like this:

PU_LAST( OBJECT ,  EVENT_LOG , PU_LAST( OBJECT, EVENT_LOG,EVENT_LOG) NOT IN (‘AUTOMATED’) AND PU_COUNT_DISTINCT(OBJECT, USER) = 1

)

.

What function is this ​@Siddharth_Gupta ? PU_NTH_LAST(). I haven’t found it in the documentation and Celonis doesn’t seem to recognize it either.

Hi ​@bhargav.bhima 

Based on your example I would try this:

CASE

WHEN PU_LAST( OBJECT, USER = 'AUTOMATED') THEN PU_LAST(OBJECT, USER, USER != 'AUTOMATED')    -- if last user is AUTOMATED, then get the last one which is not AUTOMATED

WHEN PU_LAST(OBJECT, USER) != 'AUTOMATED' THEN PU_LAST(OBJECT, USER, USER NOT IN ('AUTOMATED', PU_LAST(OBJECT, USER))  -- if the last user is NOT AUTOMATED, then get the last user that is neither AUTOMATED, nor the last user.

ELSE NULL

END

However, if multiple users happened after AUTOMATED, and you would need the last one that happened before the execution of automated, you might want to consider using the function ACTIVITY_ORDER_INDEX() to make sure you're taking the last one that happened before AUTOMATED, and not the last one that is just different.

I hope that helps!

Joao

Yes ​@joaocarlos.macie , I have checked and confirmed that PU_NTH_LAST() is not recognized in Celonis. I mistakenly confused it with another language and apologize for the oversight. Thank you for bringing this to my attention.

you don’t just want the very last non-automated user, but the one before that, provided it’s not the same as the last one. In your sample, the last non-automated user is JOHN (row 5), and the previous non-automated one is JANE (row 2), which is the expected output.

The tricky part is that nesting PU_LAST with NOT IN won’t work as expected, because it keeps re-evaluating on the same set. What you actually need is:

1. First filter out AUTOMATED (and blanks if needed).

2. Order the log by EVENT LOG ID descending.

3. Take the distinct users in that order.

4. Pick the second one.

In pseudo-formula style (adjusting to your syntax):

PU_NTH(
  OBJECT,
  EVENT_LOG,
  USER,
  EVENT_LOG NOT IN ('AUTOMATED', ''),
  2
)

each part means:

PU_NTH → lets you grab the Nth most recent value, not just the last.

EVENT_LOG NOT IN ('AUTOMATED','') → excludes automated/system entries.

2 → gives you the “last but one” user.

So in your sample, this would return JANE.

👉 If your environment doesn’t have PU_NTH, another workaround is to create two separate PU_LAST fields:

One for the last non-automated user.

One for the last non-automated user where USER <> PU_LAST(...).

That way the second one automatically resolves to the previous distinct user.

Hi!

You have to get the INDEX_ACTIVITY_ORDER of the last AUTOMATED Event and then find the Event which is the second event before that found Event.

To do that:

PU_LAST(
  "OBJECT",
  "EVENT_LOG"."USER",
  INDEX_ACTIVITY_ORDER("EVENT_LOG"."USER") + 2 =
    PU_LAST(
      "OBJECT",
      INDEX_ACTIVITY_ORDER("EVENT_LOG"."USER"),
      "EVENT_LOG"."USER" = 'AUTOMATED'
    )
)

The nested PU_LAST-Function gets the INDEX_ACTIVITY_ORDER of your last “AUTOMATED" User - in your example that would be 4.

Then you use this in the filter for the main PU_LAST-Function to get exactly the second user (+2) before the found “AUTOMATED” User.