Hi All,
I am looking for filter condition which can filter out records from activity table once specific activity is first time occurred.
Tried with REGEX, PU_FIRST but some how this doesn't work.
Is there any one applied same kind of filter condition?
Below is what I am expecting:
Anything after first event 'Approved' is what I wanted to exclude. 🙂
You could use a component filter that filters out all activities that occur later than the activity "Approved".
First you have to find the timestamp of the first occurrence of this activity. For that you can take the PU_FIRST function. As you search for an item within the same table clumn you also have to use the DOMAIN_TABLE function.
Assuming your activity table is called "ACTIVITIES" the component filter should look like this:
FILTER "ACTIVITIES"."Event Date" <= PU_FIRST(DOMAIN_TABLE("ACTIVITIES"."Event Date"), "ACTIVITIES"."Event Date", "ACTIVITIES"."Activity"='Approved)
Regards,
Florian
Hi Florian, Thanks for your help.
Tried PU_FIRST and what it's doing is returning both Approved events for same _case_key.
Also in some cases few activities appeared.
What I am looking for is how to return activities prior to first approved.
Hi Florian, Thanks for your help.
Tried PU_FIRST and what it's doing is returning both Approved events for same _case_key.
Also in some cases few activities appeared.
What I am looking for is how to return activities prior to first approved.
"Tried PU_FIRST and what it's doing is returning both Approved events for same _case_key."
-> The PU_FIRST generally only returns 1 result per case. In my query it returns the timestamp of the first occurrance of "Approved". So we get exactly 1 timestamp per case. The filter then just says "Give me all activities prior to this timestamp".
"What I am looking for is how to return activities prior to first approved."
-> That is exactly what the filter above is doing.
If you still see multiple "Approved" activities or less then you should have a closer look at those cases. Maybe some timestamps have the same value (because both activities happened at the same day)? In your example the timestamps only have dates and no times. What's the resolution of your actual timestamps? Seconds, minutes, days,..? A column with the activity timestamp in the result OLAP table could already reveil the source of the problem.
If you still get wrong results maybe you could upload a screenshot of a real case?
Best regards,
Florian
"Tried PU_FIRST and what it's doing is returning both Approved events for same _case_key."
-> The PU_FIRST generally only returns 1 result per case. In my query it returns the timestamp of the first occurrance of "Approved". So we get exactly 1 timestamp per case. The filter then just says "Give me all activities prior to this timestamp".
"What I am looking for is how to return activities prior to first approved."
-> That is exactly what the filter above is doing.
If you still see multiple "Approved" activities or less then you should have a closer look at those cases. Maybe some timestamps have the same value (because both activities happened at the same day)? In your example the timestamps only have dates and no times. What's the resolution of your actual timestamps? Seconds, minutes, days,..? A column with the activity timestamp in the result OLAP table could already reveil the source of the problem.
If you still get wrong results maybe you could upload a screenshot of a real case?
Best regards,
Florian
Hi Florian - Believe there is something goes wrong with first condition.
In below snapshot only 3 activities are populating PU_FIRST result and those are also not correct.
My activity table is named as "_CEL_SCM_ACTIVITIES".
PU_FIRST(DOMAIN_TABLE("_CEL_SCM_ACTIVITIES"."EventTime"),"_CEL_SCM_ACTIVITIES"."EventTime","_CEL_SCM_ACTIVITIES"."ActivityEN"='Release PO')
Any idea what needs to be corrected?
"Tried PU_FIRST and what it's doing is returning both Approved events for same _case_key."
-> The PU_FIRST generally only returns 1 result per case. In my query it returns the timestamp of the first occurrance of "Approved". So we get exactly 1 timestamp per case. The filter then just says "Give me all activities prior to this timestamp".
"What I am looking for is how to return activities prior to first approved."
-> That is exactly what the filter above is doing.
If you still see multiple "Approved" activities or less then you should have a closer look at those cases. Maybe some timestamps have the same value (because both activities happened at the same day)? In your example the timestamps only have dates and no times. What's the resolution of your actual timestamps? Seconds, minutes, days,..? A column with the activity timestamp in the result OLAP table could already reveil the source of the problem.
If you still get wrong results maybe you could upload a screenshot of a real case?
Best regards,
Florian
Your query gives you the timestamp of the first occurance of the "Release PO" activity for every case. I don't know if you used this query in the OLAP Table or as a filter (like I suggested).
Try the following:
In the analysis sheet you should have an OLAP with the columns "Case ID", "Activity Name" and "Activity EventTime".
If you use the following query in your component filter, then you should get the desired result.
Component filter:
FILTER "_CEL_SCM_ACTIVITIES"."EventTime" <= PU_FIRST(DOMAIN_TABLE("_CEL_SCM_ACTIVITIES"."EventTime"), "_CEL_SCM_ACTIVITIES"."EventTime", "_CEL_SCM_ACTIVITIES"."ActivityEN"='Release PO')
"Tried PU_FIRST and what it's doing is returning both Approved events for same _case_key."
-> The PU_FIRST generally only returns 1 result per case. In my query it returns the timestamp of the first occurrance of "Approved". So we get exactly 1 timestamp per case. The filter then just says "Give me all activities prior to this timestamp".
"What I am looking for is how to return activities prior to first approved."
-> That is exactly what the filter above is doing.
If you still see multiple "Approved" activities or less then you should have a closer look at those cases. Maybe some timestamps have the same value (because both activities happened at the same day)? In your example the timestamps only have dates and no times. What's the resolution of your actual timestamps? Seconds, minutes, days,..? A column with the activity timestamp in the result OLAP table could already reveil the source of the problem.
If you still get wrong results maybe you could upload a screenshot of a real case?
Best regards,
Florian
Sorry I couldn't see the screenshot when I wrote the previous answer. You have to use the query as a component filter.
"Tried PU_FIRST and what it's doing is returning both Approved events for same _case_key."
-> The PU_FIRST generally only returns 1 result per case. In my query it returns the timestamp of the first occurrance of "Approved". So we get exactly 1 timestamp per case. The filter then just says "Give me all activities prior to this timestamp".
"What I am looking for is how to return activities prior to first approved."
-> That is exactly what the filter above is doing.
If you still see multiple "Approved" activities or less then you should have a closer look at those cases. Maybe some timestamps have the same value (because both activities happened at the same day)? In your example the timestamps only have dates and no times. What's the resolution of your actual timestamps? Seconds, minutes, days,..? A column with the activity timestamp in the result OLAP table could already reveil the source of the problem.
If you still get wrong results maybe you could upload a screenshot of a real case?
Best regards,
Florian
Thanks Florian for your help.
I have managed to filter our this.
Only change what I made from your initial proposal is by updating domain table reference.
FILTER "ACTIVITIES"."Event Date" <= PU_FIRST(DOMAIN_TABLE("ACTIVITIES"."Event Date"), "ACTIVITIES"."Event Date", "ACTIVITIES"."Activity"='Approved)
Updated one -
FILTER "ACTIVITIES"."Event Date" <= PU_FIRST(DOMAIN_TABLE("_CASES_TABLE"."_CASE_KEY"), "ACTIVITIES"."Event Date", "ACTIVITIES"."Activity"='Approved)
Thanks for your support. 😊
KR,
Kalpesh
"Tried PU_FIRST and what it's doing is returning both Approved events for same _case_key."
-> The PU_FIRST generally only returns 1 result per case. In my query it returns the timestamp of the first occurrance of "Approved". So we get exactly 1 timestamp per case. The filter then just says "Give me all activities prior to this timestamp".
"What I am looking for is how to return activities prior to first approved."
-> That is exactly what the filter above is doing.
If you still see multiple "Approved" activities or less then you should have a closer look at those cases. Maybe some timestamps have the same value (because both activities happened at the same day)? In your example the timestamps only have dates and no times. What's the resolution of your actual timestamps? Seconds, minutes, days,..? A column with the activity timestamp in the result OLAP table could already reveil the source of the problem.
If you still get wrong results maybe you could upload a screenshot of a real case?
Best regards,
Florian
Oh yeah, of course you need the case table as the first parameter in the Pull-Up function. My fault!
In this case you don't need the "DOMAIN_TABLE" function. It should work with:
FILTER "ACTIVITIES"."Event Date" <= PU_FIRST("_CASES_TABLE", "ACTIVITIES"."Event Date", "ACTIVITIES"."Activity"='Approved)
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
