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