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
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)...?
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
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
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?
Hi @1461001156,
See the answer of @Eugene Em below. Hopefully that works!
Kind regards,
Jan-peter
