Skip to main content

Hi,

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.

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 .

Reply


Community Code of Conduct