What I’m trying to do is essentially pick the last but one non-automated user from the event log and pull it to object level. The previous non-automated user needs to be different from the last non-automated user.
In the sample data shown below the desired output would be JANE.
I’ve tried looping one PU_LAST and using it as filter for another PU_LAST but doesn't seem to be working.
PU_LAST( OBJECT , EVENT_LOG , EVENT_LOG NOT IN (PU_LAST( OBJECT, EVENT_LOG,EVENT_LOG NOT IN (‘AUTOMATED’) )) , ’AUTOMATED’).
EVENT LOG ID
USER
1
JACK
2
JANE
3
JOHN
4
AUTOMATED
5
JOHN
Any insights would be much appreciated.
Page 1 / 1
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
)
CASE WHEN PU_LAST(OBJECT, USER, USER != 'AUTOMATED') != PU_NTH_LAST(OBJECT, USER, 2, USER != 'AUTOMATED') THEN PU_NTH_LAST(OBJECT, USER, 2, USER != 'AUTOMATED') ELSE NULL END This PQL code retrieves the second-to-last non-automated user in a process, but only if that user is different from the last non-automated user. It uses PU_LAST to get the last non-automated user and PU_NTH_LAST with 2 to get the second-to-last one. The CASE statement ensures that the result is returned only when these two users are not the same—otherwise, it returns NULL. This helps identify meaningful user transitions, ignoring repeated or automated actions.
I hope this works .
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.
