+7For the claims. there is history type='viewing', how can we get count of other history types that has occurred other than viewing before 5 min and after 5 minutes of viewing. There are different history types
There is timestamp that captures what history type has occurred at what time, based on this I want to get the count of other history types that has occurred within 5 minutes of before and after the history type='viewing' has occurred.
Kindly help with idea for the PQL query
Hi Avishma,
As far as I understand you want to display other history types that as occurred within 5 minutes of 'viewing'. The below code should help you get the history types that occurred 5 minutes before 'viewing' other than 'viewing'
CASE WHEN
MINUTES_BETWEEN(TO_DATE(LAG(
STRING_SPLIT(
REMAP_VALUES(table.history_type,['Viewing',NULL])||'|'||
TO_STRING("table"."EVENTTIME"
,FORMAT('%Y-%m-%d %H:%M:%S')),'|',1), order by "table"."EVENTTIME", partition by "Claims"
),FORMAT('%Y-%m-%d %H:%M:%S')),
"table"."EVENTTIME")<=5
THEN
LAG(
STRING_SPLIT(
REMAP_VALUES(table.history_type,['Viewing',NULL])||'|'||
TO_STRING("table"."EVENTTIME"
, FORMAT('%Y-%m-%d %H:%M:%S')),'|',0)), order by "table"."EVENTTIME", partition by "Claims"
)
END
This code can be used as a dimension in an OLAP table that displays only the history_type='viewing'. The same code can be used by replacing 'LAG' with 'LEAD' to get the history type occurring after 5 minutes. Remap_values is used to exclude history_type='viewing'.
This looks a bit complex, you might have to tweak columns here and there. Hope this works:)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
