PQL compare dates on activity level

I actually have the same problem! :)

Hi @1461001156,

In the column Delay, you could put something like this:

CASE

WHEN

-- get the difference of two timestamps

DATEDIFF(

-- select datediff granularity (here in milliseconds)

ms,

-- select timestamp column

<table_name>."Timestamp" ,

-- select ETA timestamp of first 'Order Received' Activity

PU_FIRST(

-- set the first column as case reference domain, i.e. the PU-function uses this column

-- to select the rows that should be compaired.

DOMAIN_TABLE(<table_name>."Case_ID"),

-- select column of which the first timestamp should be taken

<table_name>."ETA",

-- set filter to only use the order received row

Activity = 'Order received'

)

) > 0 -- select if datediff should be higher or lower than zero

THEN 1.0

ELSE 0.0

END

Hope that this works for you!

Kind regards,

Jan-peter

See that the markup is deleted, sorry for that!

Hi @janpeter.van.d , thanks so much for your detailed answer! I guess this could work, however, it says that column "ETA" can't be found (I guess it's because "ETA" is a column which I manually created as an attribute). Is there a way how I can make the column visibile (e.g. changing a setting in the attribute)...?

As you can see in the Screenshot, the column is somehow unknown (I created it as an attribute). Do you know a way how i can make it visible?

@1461001156 

What are you using to define ETA as an attribute?

If you used a PQL expression to create it, simply replace your <table_name>."ETA" with the PQL expression you used to create the ETA attribute in the PU function, and it should work.

Separately, when using PU_FIRST I would definitely recommend using ORDER BY expression to make sure you are truly pulling the first value. Alternatively, you can use PU_MIN, in that case it will pick up the earliest timestamp regardless of order in the underlying table.

Eugene

Hi @1461001156,

See the answer of @Eugene Em below. Hopefully that works!

Kind regards,

Jan-peter