Skip to main content
Question

Counting rows that share the same case id

  • June 23, 2023
  • 3 replies
  • 4 views
A
Level 2

Hi there,

this probably has some super simple solution but im kind of stuck at the moment.

I basically want to count, how often a certain activity happens per case. My idea was to apply a filter so that i only have rows containing that activity. For example i now have 4 collums with case id 1. Now i just want to create a new collum that shows how often the activity was done in each case by just counting how many rows share the same case id.

For example of case id 1, there should be a collum at the end, that shows "4" in every row with the case id 1.

Thanks for your help once again and kind regards

Aaron

3 replies

abhishek.chatu14
Level 11
Forum|alt.badge.img+4

I am little confused with the question with respect to all activities or just a particular activity, however will try to answer both.

 

Let's say its a particular activity, use an olap table take first column as Table.CaseID and for the count use the following

 

COUNT (CASE WHEN TABLE.ACTIVITY_NAME='particular activity' THEN 1 ELSE 0 END)

 

In case its all activities use the following

 

COUNT (TABLE.ACTIVITY_NAME)

 

I hope I this helps!

A
Level 2
  • aaron.pesch
  • Author
  • Level 2
  • June 24, 2023

Hi,

if I understood your answer correctly, it isn't quite what I was looking for. Let me try to explain it a bit better.

I have filtered my process to only have the 'Investigate' followed by 'Follow Up' activities shown.

Now I want to count how often the 'Follow Up' activity was executed per case. So f.e. in the case of CASEID=5, I would expect to have the row "Count follow up" only contain 3s since 'Follow Up' was executed 3 times.

I thought since we have the filter anyways, I just need to count how many times there is a row with the same CASEID, but I couldn't make it work/construct a working PQL formula.

 

Maybe this makes it better to understand.

image1

G
Level 12

Try INDEX_ACTIVITY_TYPE and get the max per case. Filtered by the activity you want to count. That will give you the number of times that activity happened.

Community Code of ConductAccessibility statement