How to get the value from the previous activity

#1

Hello everyone,

I want to find out how many Documents are being returned to Departments for correction as a result of Revision. So I have 2 Activities: ‘Revision’, which has a number of the department that created the document (and hence triggered the Event ‘Revision’) and the event ‘Document returned for correction’ which holds the number of the department that revised the document.
So the path would be to find the cases with documents that have ‘Document returned for correction’ -event and go backwards to ‘Revision’ to get the department number.
Any idea how to implement that?
Thank you

#2

Hi,

a possible solution for that can be the following code:

CASE WHEN PROCESS EQUALS 'Document returned for correction’
THEN PU_FIRST(“CASES”, “TABLE”.“DEPARTMENTNUMBER” , “ACTIVITIES”.“ACTIVITY_EN” = ‘Revision’) ELSE NULL END

please let me know if this works for you.

Best regards,
Viana

#3

Hello Viana,

the idea goes in the right direction, however ‘Revision’ can occure multiple times pro CASE, so I really need the Revision previous to the “Document returned for correction” and not just the PU_FIRST one.

Thanks!

#4

Hi,

you can then use the following Code:

CASE WHEN PROCESS EQUALS 'Document returned for correction’
THEN
(CASE WHEN “ACTIVITIES”.“ACTIVITY_EN” = ‘Revision’ THEN ''TABLE”.“DEPARTMENTNUMBER”
ELSE NULL END)
ELSE NULL END

Best regards,
Viana

#5

The code produces only empty (NULL) values, which is wrong.

#6

Hi,

If I correctly understand your question,you want to have the department numbers associated with those ‘Revision’ activities that immediately resulted in a document being returned for corrections. As you didn’t mention which table the department number was listed in, I made the assumption that it is a column in your Activity table. If this is the case then the following code should return the desired department numbers:

CASE WHEN
MATCH_PROCESS("Your_activity_table"."ACTIVITY_EN", NODE['Revision'] as src NODE['Document returned for correction'] as trg CONNECTED BY DIRECT [ src, trg ]) > 0
THEN (CASE WHEN "Your_activity_table"."ACTIVITY_EN" = 'Revision' THEN "Your_activity_table"."Your department number column" ELSE NULL END)
ELSE NULL 
END

If the department number isn’t written in your activity table then please write back with a more detailed description of your data model.

I hope this helps

Best regards,
Viana

#7

Hello,

it doesn’t work. I used the following code:

CASE WHEN
MATCH_PROCESS(“CEL_LBU_ACTIVITIES”.“ACTIVITY_DE”, NODE[‘Revision’] as src, NODE[‘Revisionsrückgabe’] as trg CONNECTED BY DIRECT [ src, trg ]) > 0
THEN (CASE WHEN “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE” = ‘Revision’ THEN “CEL_LBU_ACTIVITIES”.“OE_NR” ELSE NULL END)
ELSE NULL
END

and it delivers NULL no matter which column of the activity table I take. Every ‘Revision’ has an ‘OE_NR’, so it can’t be NULL for ALL the cases in my DB. The ‘OE_NR’ also belongs to the same activity table.

Best regards,
Maria

#8

Hi Maria,

You can use the SOURCE / TARGET operators for that:

SOURCE ( "CEL_LBU_ACTIVITIES"."OE_NR", 
CASE WHEN "CEL_LBU_ACTIVITIES"."ACTIVITY_DE" = 'Revision' OR "CEL_LBU_ACTIVITIES"."ACTIVITY_DE" = 'Revisionsrückgabe' 
THEN "CEL_LBU_ACTIVITIES"."ACTIVITY_DE" 
ELSE NULL END )

This outputs a column of all source activities of all edges that include ‘Revision’ and ‘Revisionsrückgabe’ activities.
You can add a column with the corresponding case using

"CEL_LBU_ACTIVITIES"."CASE_ID"

If there are multiple Revisions following each other without a ‘return’ event, those departments also appear in the above solution. To get rid of those, you can add the following filter statement to your component:

FILTER SOURCE ( "CEL_LBU_ACTIVITIES"."ACTIVITY_DE", 
CASE WHEN "CEL_LBU_ACTIVITIES"."ACTIVITY_DE" = 'Revision' OR "CEL_LBU_ACTIVITIES"."ACTIVITY_DE" = 'Revisionsrückgabe' 
THEN "CEL_LBU_ACTIVITIES"."ACTIVITY_DE" 
ELSE NULL END ) = 'Revision' 
AND TARGET ( "CEL_LBU_ACTIVITIES"."ACTIVITY_DE") = 'Revisionsrückgabe';

For each occurence of ‘Revisionsrückgabe’, this should output the value in the “CEL_LBU_ACTIVITIES”.“OE_NR” column related to the most recent ‘Revision’ activity.

Best regards
David

2 Likes
#9

Hello David,

the filter seems to fail. I am getting the “no common parent between tables” message.

Best,
Maria

#10

Hello Maria,

so only the filter fails, and the table query works?
Which Celonis version do you use?

Best regards,
David

#11

Hello Maria,

I think I may have found the error. Instead of

you need to use the case column of your case table, i.e. something like this:

"CEL_LBU_ACTIVITIES_CASES"."CASE_ID"

Sorry for that mistake! Let me know if you encounter more errors.

David

#12

Hi David,

it is still the problem with the Filter:( the same one. In addition, I would like to aggregate on those departments. Like the “Documents returned”/“Revisions” by departments to use as a quality check.

Best,
Maria

#13

Hi Maria,

In order to help you finding the error, could you please provide us the queries that you are using in the dimensions and the filter, as well as the exact error message you see? Which Celonis version are you using (CPM4.5, CPM4.4, IBC,…)?

Thanks and best regards
David

#14

Hello David,
thank you so much for your support!

We currently have CPM4.4. The queries look like that:

FILTER:
FILTER SOURCE (“CEL_LBU_ACTIVITIES”.“ACTIVITY_DE”,
CASE WHEN “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE” = ‘Revision’ OR “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE” = ‘Revisionsrückgabe’
THEN “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE”
ELSE NULL END ) = ‘Revision’
AND TARGET (“CEL_LBU_ACTIVITIES”.“ACTIVITY_DE”) = ‘Revisionsrückgabe’;

OLAP-Table with Dimensions:

  1. SOURCE ( “CEL_LBU_ACTIVITIES”.“OE_NR”,
    CASE WHEN “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE” = ‘Revision’ OR “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE” = ‘Revisionsrückgabe’
    THEN “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE”
    ELSE NULL END )
  2. “CEL_LBU_CASES”.“SDNR”
  3. TARGET(“CEL_LBU_ACTIVITIES”.“ACTIVITY_DE”)

Error:
Error

If it is the Celonis Version Problem then I’m just waiting until we have 4.5.

Best regards,
Maria

#15

Hi Maria,

CPM4.4 is fine. You need to remove the second dimension in your OLAP table, then it should work. If you want to display the “CEL_LBU_CASES”.“SDNR” field, you need to wrap it into SOURCE or TARGET, depending on whether you want to display the value related to OE_NR or ACTIVITY_DE.

Best,
David

#16

Hi Maria,

sorry, I didn’t see that “CEL_LBU_CASES”.“SDNR” is your case table.
I just discovered that in 4.4, you need to specify the CASE WHEN in ALL occurences of SOURCE or TARGET in the filter statement.
So you will need to rewrite your FILTER to:

FILTER SOURCE (“CEL_LBU_ACTIVITIES”.“ACTIVITY_DE”,
CASE WHEN “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE” = ‘Revision’ OR “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE” = ‘Revisionsrückgabe’
THEN “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE”
ELSE NULL END ) = ‘Revision’
AND TARGET (“CEL_LBU_ACTIVITIES”.“ACTIVITY_DE”, 
CASE WHEN “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE” = ‘Revision’ OR “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE” = ‘Revisionsrückgabe’
THEN “CEL_LBU_ACTIVITIES”.“ACTIVITY_DE”
ELSE NULL END) = ‘Revisionsrückgabe’;

Sorry! Hope this works now.

David

2 Likes
#17

Thank you and it works!

1 Like