Skip to main content

Celonis Product Documentation

LINK_FILTER
Description

LINK_FILTER enables case-based recursive filtering. Given a filter statement, LINK_FILTER first maps the filter to cases and then extends the resulting filter to cover all preceding or succeeding cases (according to the signal link configuration).

The signal link configuration connects activities (more precisely, rows in the corresponding activity tables) from different cases and activity tables using signal columns, resulting in a virtual edge table of directed edges between rows from the activity tables.

LINK_FILTER filters the edges on a virtual edge table which is the output table of LINK_SOURCE/LINK_TARGET.

Syntax
 LINK_FILTER( filter_expression, {ANCESTORS|DESCENDANTS} )

 LINK_FILTER( BIND_FILTERS ( activity_table, filter_expression ), {ANCESTORS|DESCENDANTS} )

  • filter_expression needs to be a column from the activity table.

  • If a filter_expression neither belongs to an activity table nor is based on LINK_SOURCE/LINK_TARGET, it can first be pulled to an activity table using the BIND_FILTERS operator.

  • ANCESTORS filters on the activity and all ancestors of that activity, while DESCENDANTS filters on the activity and all descendants of that activity.

  • LINK_FILTER can be used together with FILTER and CASE WHEN statements.

 LINK_FILTER_ORDERED( filter_expression, {ANCESTORS|DESCENDANTS} )

 LINK_FILTER_ORDERED( BIND_FILTERS ( activity_table, filter_expression ), {ANCESTORS|DESCENDANTS} )

  • The ORDERED version of LINK_FILTER considers the order of the activity table based on the timestamps. For example, in contrast to the regular LINK_FILTER, only outgoing links of an activity that takes place after the activity with the incoming link are considered.

NULL handling

  • NULL values in the OUT and IN columns from the signal link configuration are ignored.

Examples

[1]

Simple example of LINK_FILTER on a single Activity table. The OUT column of the Activity table is specified as the outgoing edge and the IN column is specified as the incoming edge. LINK_FILTER filters on activity B and all descendants of B.

As activities C and D are descendants of B based on the outgoing and incoming links, they are included in the result. The result therefore contains the edges B ➔ C and C ➔ D.

Query

Filter 

         FILTER LINK_FILTER ( "Activities"."ACTIVITY" = 'B' , DESCENDANTS );

Column1 

         LINK_SOURCE ( "Activities"."ACTIVITY" )

Column2 

         LINK_TARGET ( "Activities"."ACTIVITY" )

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'1'

'A'

Thu Dec 01 2016 13:00:00.000

null

null

'1'

'B'

Thu Dec 01 2016 13:01:00.000

'a'

null

'2'

'C'

Thu Dec 01 2016 13:02:00.000

'b'

'a'

'2'

'D'

Thu Dec 01 2016 13:03:00.000

null

'b'

'3'

'E'

Thu Dec 01 2016 13:04:00.000

'c'

null

'4'

'F'

Thu Dec 01 2016 13:05:00.000

null

'c'

Result

Column1 : string

Column2 : string

'B'

'C'

'C'

'D'

[2]

Simple example of LINK_FILTER on a single Activity table. The OUT column of the Activity table is specified as the outgoing edge and the IN column is specified as the incoming edge. LINK_FILTER filters on activity D and all ancestors of D.

As activities B and C are ancestors of D based on the outgoing and incoming links, they are included in the result. The result therefore contains the edges B ➔ C and C ➔ D.

Query

Filter 

         FILTER LINK_FILTER ( "Activities"."ACTIVITY" = 'D' , ANCESTORS );

Column1 

         LINK_SOURCE ( "Activities"."ACTIVITY" )

Column2 

         LINK_TARGET ( "Activities"."ACTIVITY" )

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'1'

'A'

Thu Dec 01 2016 13:00:00.000

null

null

'1'

'B'

Thu Dec 01 2016 13:01:00.000

'a'

null

'2'

'C'

Thu Dec 01 2016 13:02:00.000

'b'

'a'

'2'

'D'

Thu Dec 01 2016 13:03:00.000

null

'b'

'3'

'E'

Thu Dec 01 2016 13:04:00.000

'c'

null

'4'

'F'

Thu Dec 01 2016 13:05:00.000

null

'c'

Result

Column1 : string

Column2 : string

'B'

'C'

'C'

'D'

[3]

Simple example of LINK_FILTER on a single Activity table. The OUT column of the Activity table is specified as the outgoing edge and the IN column is specified as the incoming edge. LINK_FILTER filters on activity B and all descendants of B.

There is a link from activity B (case 1) to activity D (case 2). Case 2 has another outgoing link from activity C to activity F of case 4. The result therefore contains the edges B ➔ D and C ➔ F.

Query

Filter 

         FILTER LINK_FILTER ( "Activities"."ACTIVITY" = 'B' , DESCENDANTS );

Column1 

         LINK_SOURCE ( "Activities"."ACTIVITY" )

Column2 

         LINK_TARGET ( "Activities"."ACTIVITY" )

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'1'

'A'

Thu Dec 01 2016 13:00:00.000

null

null

'1'

'B'

Thu Dec 01 2016 13:01:00.000

'a'

null

'2'

'C'

Thu Dec 01 2016 13:02:00.000

'c'

null

'2'

'D'

Thu Dec 01 2016 13:03:00.000

null

'a'

'3'

'E'

Thu Dec 01 2016 13:04:00.000

null

null

'4'

'F'

Thu Dec 01 2016 13:05:00.000

null

'c'

Result

Column1 : string

Column2 : string

'B'

'D'

'C'

'F'

[4]

Simple example of LINK_FILTER_ORDERED on a single Activity table. The OUT column of the Activity table is specified as the outgoing edge and the IN column is specified as the incoming edge. LINK_FILTER_ORDERED filters on activity B and all descendants of B.

There is a link from activity B (case 1) to activity D (case 2). Case 2 has another outgoing link from activity C to activity F of case 4, however, this happens before the incoming link. Since LINK_FILTER_ORDERED is used, only the edge B ➔ D is contained in the result.

Query

Filter 

         FILTER LINK_FILTER_ORDERED ( "Activities"."ACTIVITY" = 'B' , DESCENDANTS );

Column1 

         LINK_SOURCE ( "Activities"."ACTIVITY" )

Column2 

         LINK_TARGET ( "Activities"."ACTIVITY" )

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'1'

'A'

Thu Dec 01 2016 13:00:00.000

null

null

'1'

'B'

Thu Dec 01 2016 13:01:00.000

'a'

null

'2'

'C'

Thu Dec 01 2016 13:02:00.000

'c'

null

'2'

'D'

Thu Dec 01 2016 13:03:00.000

null

'a'

'3'

'E'

Thu Dec 01 2016 13:04:00.000

null

null

'4'

'F'

Thu Dec 01 2016 13:05:00.000

null

'c'

Result

Column1 : string

Column2 : string

'B'

'D'

[5]

Simple example of LINK_FILTER on the Case table. The OUT column of the Activity table is specified as the outgoing edge and the IN column is specified as the incoming edge. LINK_FILTER filters on case value 5, which corresponds to case 1 and case 3.

Case 1 has an outgoing link from activity B, which means that it filters on activity B and all descendants of it. As activities C and D are descendants of B based on the outgoing and incoming links, they are included in the result.

Case 3 has an outgoing link from activity E, which means that it filters on activity E and all descendants of it. As activity F is a descendant of E based on the outgoing and incoming links, it is also included in the result.

The result therefore contains the edges B ➔ C, C ➔ D and E ➔ F.

Query

Filter 

         FILTER LINK_FILTER ( BIND_FILTERS ( "Activities" , "Cases"."Value" = 5 ) , DESCENDANTS );

Column1 

         LINK_SOURCE ( "Activities"."ACTIVITY" )

Column2 

         LINK_TARGET ( "Activities"."ACTIVITY" )

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'1'

'A'

Thu Dec 01 2016 13:00:00.000

null

null

'1'

'B'

Thu Dec 01 2016 13:01:00.000

'a'

null

'2'

'C'

Thu Dec 01 2016 13:02:00.000

'b'

'a'

'2'

'D'

Thu Dec 01 2016 13:03:00.000

null

'b'

'3'

'E'

Thu Dec 01 2016 13:04:00.000

'c'

null

'4'

'F'

Thu Dec 01 2016 13:05:00.000

null

'c'

Cases

CASE_ID : string

Value : int

'1'

5

'2'

7

'3'

5

'4'

11

Foreign Keys

Cases.CASE_ID

Activities.CASE_ID

Result

Column1 : string

Column2 : string

'B'

'C'

'C'

'D'

'E'

'F'

[6]

Example of LINK_FILTER and LINK_SOURCE / LINK_TARGET on three Activity tables. The OUT columns of the Activity tables are specified as the outgoing edges of the corresponding tables, and the IN columns are specified as the incoming edges of the corresponding table. LINK_FILTER filters on case CI1 of Activity table EI.

Activity B of case CI1 has an outgoing link to activity G of case CII1 contained in Activity table EII. Activity E of this case links to Activity M and P (cases CIII1 and CIII2 of Activity table EIII).

The result therefore contains the edges B ➔ G, E ➔ M and E ➔ P.

Query

Filter 

         FILTER ( LINK_FILTER ( "EI"."CASE_ID" = 'CI1' , DESCENDANTS ) );

Column1 

         LINK_SOURCE ( "EI"."ACTIVITY" , "EII"."ACTIVITY" )

Column2 

         LINK_TARGET ( "EII"."ACTIVITY" , "EIII"."ACTIVITY" )

Input

Output

CI

CASE_ID : string

NETWR : float

'CI1'

5.0

'CI2'

6.0

EI

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

'CI1'

'A'

Thu Dec 01 2016 13:00:00.000

null

'CI1'

'B'

Thu Dec 01 2016 13:01:00.000

'b'

'CI2'

'C'

Thu Dec 01 2016 13:02:00.000

'e'

'CI2'

'D'

Thu Dec 01 2016 13:03:00.000

null

EII

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CII1'

'E'

Thu Dec 01 2016 13:00:00.000

'c'

null

'CII1'

'F'

Thu Dec 01 2016 13:01:00.000

null

null

'CII1'

'G'

Thu Dec 01 2016 13:02:00.000

null

'b'

'CII2'

'H'

Thu Dec 01 2016 13:03:00.000

'd'

'c'

'CII2'

'I'

Thu Dec 01 2016 13:04:00.000

null

null

EIII

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

IN : string

'CIII1'

'M'

Thu Dec 01 2016 13:00:00.000

'c'

'CIII1'

'N'

Thu Dec 01 2016 13:01:00.000

null

'CIII2'

'P'

Thu Dec 01 2016 13:02:00.000

'c'

'CIII2'

'Q'

Thu Dec 01 2016 13:03:00.000

null

Foreign Keys

CI.CASE_ID

EI.CASE_ID

Result

Column1 : string

Column2 : string

'B'

'G'

'E'

'M'

'E'

'P'

[7]

Example of LINK_FILTER_ORDERED and LINK_SOURCE / LINK_TARGET on three Activity tables. The OUT columns of the Activity tables are specified as the outgoing edges of the corresponding tables, and the IN columns are specified as the incoming edges of the corresponding table. LINK_FILTER_ORDERED filters on case CI1 of Activity table EI.

Activity B of case CI1 has an outgoing link to activity G of case CII1 contained in Activity table EII. There is no outgoing link from case CII1 after activity G, which is why edge B ➔ G is the only edge in the result.

Query

Filter 

         FILTER ( LINK_FILTER_ORDERED ( "EI"."CASE_ID" = 'CI1' , DESCENDANTS ) );

Column1 

         LINK_SOURCE ( "EI"."ACTIVITY" , "EII"."ACTIVITY" )

Column2 

         LINK_TARGET ( "EII"."ACTIVITY" , "EIII"."ACTIVITY" )

Input

Output

CI

CASE_ID : string

NETWR : float

'CI1'

5.0

'CI2'

6.0

EI

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

'CI1'

'A'

Thu Dec 01 2016 13:00:00.000

null

'CI1'

'B'

Thu Dec 01 2016 13:01:00.000

'b'

'CI2'

'C'

Thu Dec 01 2016 13:02:00.000

'e'

'CI2'

'D'

Thu Dec 01 2016 13:03:00.000

null

EII

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CII1'

'E'

Thu Dec 01 2016 13:00:00.000

'c'

null

'CII1'

'F'

Thu Dec 01 2016 13:01:00.000

null

null

'CII1'

'G'

Thu Dec 01 2016 13:02:00.000

null

'b'

'CII2'

'H'

Thu Dec 01 2016 13:03:00.000

'd'

'c'

'CII2'

'I'

Thu Dec 01 2016 13:04:00.000

null

null

EIII

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

IN : string

'CIII1'

'M'

Thu Dec 01 2016 13:00:00.000

'c'

'CIII1'

'N'

Thu Dec 01 2016 13:01:00.000

null

'CIII2'

'P'

Thu Dec 01 2016 13:02:00.000

'c'

'CIII2'

'Q'

Thu Dec 01 2016 13:03:00.000

null

Foreign Keys

CI.CASE_ID

EI.CASE_ID

Result

Column1 : string

Column2 : string

'B'

'G'

[8]

Example of LINK_FILTER using DESCENDANTS and CASE WHEN on a single Activity table. The OUT column of the table is specified as the outgoing edges and the IN columns are specified as the incoming edges.

LINK_FILTER performs the filtering on the edges B ➔ C and C ➔ D. The first edge has B as its OUT activity, therefore it is a descendant of B. The second edge has C as its OUT activity which is the IN activity of the first edge. Hence, it is also a descendant of activity B.

As a result, both edges evaluate to true. Since we only have two edges, the output table is of size two.

Query

Column1 

         CASE WHEN LINK_FILTER ( "Activities"."ACTIVITY" = 'B' , DESCENDANTS ) THEN 1 ELSE 0 END

Input

Output

Activities

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'1'

'A'

Thu Dec 01 2016 13:00:00.000

null

null

'1'

'B'

Thu Dec 01 2016 13:01:00.000

'a'

null

'2'

'C'

Thu Dec 01 2016 13:02:00.000

'b'

'a'

'2'

'D'

Thu Dec 01 2016 13:03:00.000

null

'b'

'3'

'E'

Thu Dec 01 2016 13:04:00.000

null

null

Result

Column1 : int

1

1

See also: