Skip to main content

Hi ,

I am trying to use a PU function and use the result as a filter within another PU function.

When i try below PQL it works fine.

PU_LAST( OBJECT, EVENT_LOG,EVENT_LOG NOT IN ( PU_LAST(OBJECT,EVENT_LOG)))

But when i try to add another argument for the ‘not in ‘ it returns nulls . In this example the ‘ABC’ is the additional argument for the ‘not in’ 

PU_LAST( OBJECT, EVENT_LOG,EVENT_LOG NOT IN ( PU_LAST(OBJECT,EVENT_LOG),’ABC’)) .

have also tried splitting the filter using AND but still same issue persists.

Any insights would be much appreciated.

Hi bhargav, 

The PU_LAST function in your PQL is returning nulls because the NOT IN clause can behave unexpectedly when its comparison list contains NULL values. If the inner PU_LAST(OBJECT, EVENT_LOG) evaluates to NULL for any object, the filter EVENT_LOG NOT IN (NULL, 'ABC') will yield an UNKNOWN result for comparisons, which Celonis treats as FALSE, causing those records to be excluded and the outer PU_LAST to potentially return NULL. To fix this, you should use COALESCE to replace any NULL results from the inner PU_LAST with a unique, guaranteed-not-to-exist placeholder string (e.g., '###_NULL_PLACEHOLDER_###') within the NOT IN list, ensuring all elements in the comparison list are non-NULL. Hope it helps, thank you.

Hi bhargav, 

The PU_LAST function in your PQL is returning nulls because the NOT IN clause can behave unexpectedly when its comparison list contains NULL values. If the inner PU_LAST(OBJECT, EVENT_LOG) evaluates to NULL for any object, the filter EVENT_LOG NOT IN (NULL, 'ABC') will yield an UNKNOWN result for comparisons, which Celonis treats as FALSE, causing those records to be excluded and the outer PU_LAST to potentially return NULL. To fix this, you should use COALESCE to replace any NULL results from the inner PU_LAST with a unique, guaranteed-not-to-exist placeholder string (e.g., '###_NULL_PLACEHOLDER_###') within the NOT IN list, ensuring all elements in the comparison list are non-NULL. Hope it helps, thank you.

Hi Navya,

Thanks for your insights .

I tried out unit testing with cases where only non null values will be returned by the inner PU_LAST and still see null values being returned by the outer PU_LAST.

 

Hi ,

I am trying to use a PU function and use the result as a filter within another PU function.

When i try below PQL it works fine.

PU_LAST( OBJECT, EVENT_LOG,EVENT_LOG NOT IN ( PU_LAST(OBJECT,EVENT_LOG)))

But when i try to add another argument for the ‘not in ‘ it returns nulls . In this example the ‘ABC’ is the additional argument for the ‘not in’ 

PU_LAST( OBJECT, EVENT_LOG,EVENT_LOG NOT IN ( PU_LAST(OBJECT,EVENT_LOG),’ABC’)) .

have also tried splitting the filter using AND but still same issue persists.

Any insights would be much appreciated.

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  
1 JACK
2 JANE
3 JOHN
4 AUTOMATED
5 JOHN

 

 

Reply


Community Code of Conduct