Skip to main content

Celonis Product Documentation

LINK_FILTER
Description

Warning

To use this feature, Object Link needs to be configured in the data model.

LINK_FILTER enables case-based recursive filtering on the Object Link graph.

The Object Link configuration connects rows belonging to specified tables, resulting in a virtual graph structure. Each row is represented by a node in the graph. The graph structure is represented by an edge table of directed edges between rows from the tables. This can be defined by using signal columns or by using a dedicated mapping table, which contains all existing edges.

The LINK_FILTER operator can be used to filter along the edges of the Object Link graph. The user can specify a condition defining the starting point and the direction in which the graph should be traversed to apply the filter.

LINK_FILTER returns Boolean values and can therefore be used in all places where a condition is expected. Usually it is used as the condition of a FILTER or CASE WHEN statement.

LINK_FILTER starts a traversal of the graph with the nodes of the graph that fulfill the given condition. All nodes that are passed with the traversal are included in the filter result. The traversal is done in the direction that was specified. 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 Object Link configuration)

Syntax
 LINK_FILTER ( filter_expression, {ANCESTORS|DESCENDANTS} [, number_of_hops] )

 LINK_FILTER ( BIND_FILTERS ( object_link_table, filter_expression ), {ANCESTORS|DESCENDANTS} [, number_of_hops] )

  • filter_expression needs to be a column from the Object Link table.

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

  • ANCESTORS filters on the row and all ancestors of that row in the Object Link graph, while DESCENDANTS filters on the row and all descendants of that row.

  • number_of_hops (optional) is an INT parameter that specifies how many levels to include in the link traversal process. Everything above this level will be not be included in the result.

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

 LINK_FILTER_ORDERED ( filter_expression, {ANCESTORS|DESCENDANTS} [, number_of_hops] )

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

  • 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.

  • LINK_FILTER_ORDERED can only be used with a Signal Link setup.

NULL handling

  • NULL values in the OUT and IN columns from the Object Link configuration are ignored.

Examples

[1]

Example of LINK_FILTER and LINK_SOURCE/LINK_TARGET on two activity tables. The OUT columns of the activity tables are specified as outgoing edges of the corresponding tables, and the IN columns as incoming edges of the corresponding tables.

LINK_FILTER filters on activity E and all descendants of activity E. The filter is mapped to the case containing activity E, case CI2. This case has two outgoing links, one from activity D to activity F and one from activity E to activity H of activity table EII. The corresponding case CII1 has another outgoing link from activity G to activity K. So the result contains the edges D ➔ F, E ➔ H and G ➔ K.

Query

Filter 

         FILTER LINK_FILTER ( "EI"."ACTIVITY" = 'E' , DESCENDANTS );

Column1 

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

Column2 

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

Input

Output

CI

CASE_ID : string

VALUE : int

'CI1'

5

'CI2'

3

CII

CASE_ID : string

VALUE : int

'CII1'

1

'CII2'

4

'CII3'

2

EI

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CI1'

'A'

Mon Jan 23 2023 14:00:00.000

'a'

null

'CI1'

'B'

Mon Jan 23 2023 14:01:00.000

'b'

'a'

'CI1'

'C'

Mon Jan 23 2023 14:02:00.000

null

null

'CI2'

'D'

Mon Jan 23 2023 14:03:00.000

'c'

'b'

'CI2'

'E'

Mon Jan 23 2023 14:04:00.000

'd'

null

'CI2'

'F'

Mon Jan 23 2023 14:05:00.000

null

'c'

EII

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CII1'

'G'

Mon Jan 23 2023 14:00:00.000

'e'

null

'CII1'

'H'

Mon Jan 23 2023 14:01:00.000

null

'd'

'CII2'

'I'

Mon Jan 23 2023 14:02:00.000

'f'

null

'CII2'

'J'

Mon Jan 23 2023 14:03:00.000

null

'f'

'CII3'

'K'

Mon Jan 23 2023 14:04:00.000

null

'e'

'CII3'

'L'

Mon Jan 23 2023 14:05:00.000

null

null

Foreign Keys

CI.CASE_ID

EI.CASE_ID

CII.CASE_ID

EII.CASE_ID

Result

Column1 : string

Column2 : string

'D'

'F'

'E'

'H'

'G'

'K'

[2]

Example of LINK_FILTER and LINK_SOURCE/LINK_TARGET on two case tables. The OUT columns of the tables are specified as outgoing edges of the corresponding tables, and the IN columns as incoming edges of the corresponding tables.

LINK_FILTER filters on case C2 and all ancestors of it. The filter is propagated along the incoming edges of C2. This case has one incoming link from case B1. Case B1 has an incoming link from case A1. Therefore, the result contains the edges A1 ➔ B1 and B1 ➔ C2.

link-filter-case-link-ancestor-example.png

Query

Filter 

         FILTER LINK_FILTER ( "CASE_C"."CASE_ID" = 'C2' , ANCESTORS );

Column1 

         LINK_SOURCE ( "CASE_A"."CASE_ID" , "CASE_B"."CASE_ID" )

Column2 

         LINK_TARGET ( "CASE_B"."CASE_ID" , "CASE_C"."CASE_ID" )

Input

Output

Case_A

CASE_ID : string

OUT : string

'A1'

'a'

'A2'

'b'

Case_B

CASE_ID : string

OUT : string

IN : string

'B1'

'c'

'a'

'B2'

null

'b'

Case_C

CASE_ID : string

IN : string

'C1'

'c'

'C2'

'c'

Result

Column1 : string

Column2 : string

'A1'

'B1'

'B1'

'C2'

[3]

Example of LINK_FILTER_ORDERED and LINK_SOURCE/LINK_TARGET on two activity tables. The OUT columns of the activity tables are specified as outgoing edges of the corresponding tables, and the IN columns as incoming edges of the corresponding tables.

LINK_FILTER_ORDERED filters on activity B and all descendants of activity B. The filter is mapped to the case containing activity B, case CI1. This case has two outgoing links, one from activity A to activity B and one from activity B to activity D. Activity D has an outgoing link to activity F. The corresponding case CI2 has another outgoing link from activity E to activity H of activity table EII. The corresponding case CII1 has an outgoing link from activity G. However, as the outgoing link from activity G comes before the incoming link to activity H, this link is ignored. So the result contains the edges A ➔ B, B ➔ D, D ➔ F and E ➔ H

Query

Filter 

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

Column1 

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

Column2 

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

Input

Output

CI

CASE_ID : string

VALUE : int

'CI1'

5

'CI2'

3

CII

CASE_ID : string

VALUE : int

'CII1'

1

'CII2'

4

'CII3'

2

EI

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CI1'

'A'

Mon Jan 23 2023 14:00:00.000

'a'

null

'CI1'

'B'

Mon Jan 23 2023 14:01:00.000

'b'

'a'

'CI1'

'C'

Mon Jan 23 2023 14:02:00.000

null

null

'CI2'

'D'

Mon Jan 23 2023 14:03:00.000

'c'

'b'

'CI2'

'E'

Mon Jan 23 2023 14:04:00.000

'd'

null

'CI2'

'F'

Mon Jan 23 2023 14:05:00.000

null

'c'

EII

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CII1'

'G'

Mon Jan 23 2023 14:00:00.000

'e'

null

'CII1'

'H'

Mon Jan 23 2023 14:01:00.000

null

'd'

'CII2'

'I'

Mon Jan 23 2023 14:02:00.000

'f'

null

'CII2'

'J'

Mon Jan 23 2023 14:03:00.000

null

'f'

'CII3'

'K'

Mon Jan 23 2023 14:04:00.000

null

'e'

'CII3'

'L'

Mon Jan 23 2023 14:05:00.000

null

null

Foreign Keys

CI.CASE_ID

EI.CASE_ID

CII.CASE_ID

EII.CASE_ID

Result

Column1 : string

Column2 : string

'A'

'B'

'B'

'D'

'D'

'F'

'E'

'H'

[4]

Example of LINK_FILTER and LINK_SOURCE/LINK_TARGET on two activity tables. The OUT columns of the activity tables are specified as outgoing edges of the corresponding tables, and the IN columns as incoming edges of the corresponding tables.

LINK_FILTER filters on case value 3 of case table CI, which corresponds to case CI2. Case CI2 has two outgoing links, one link from activity D to activity F and another link from activity E to activity H of activity table EII. The corresponding case CII1 has one more outgoing link from activity G to activity K. The result thus contains the edges D ➔ F, E ➔ H and G ➔ K.

Query

Filter 

         FILTER LINK_FILTER ( BIND_FILTERS ( "EI" , "CI"."VALUE" = 3 ) , DESCENDANTS );

Column1 

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

Column2 

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

Input

Output

CI

CASE_ID : string

VALUE : int

'CI1'

5

'CI2'

3

CII

CASE_ID : string

VALUE : int

'CII1'

1

'CII2'

4

'CII3'

2

EI

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CI1'

'A'

Mon Jan 23 2023 14:00:00.000

'a'

null

'CI1'

'B'

Mon Jan 23 2023 14:01:00.000

'b'

'a'

'CI1'

'C'

Mon Jan 23 2023 14:02:00.000

null

null

'CI2'

'D'

Mon Jan 23 2023 14:03:00.000

'c'

'b'

'CI2'

'E'

Mon Jan 23 2023 14:04:00.000

'd'

null

'CI2'

'F'

Mon Jan 23 2023 14:05:00.000

null

'c'

EII

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CII1'

'G'

Mon Jan 23 2023 14:00:00.000

'e'

null

'CII1'

'H'

Mon Jan 23 2023 14:01:00.000

null

'd'

'CII2'

'I'

Mon Jan 23 2023 14:02:00.000

'f'

null

'CII2'

'J'

Mon Jan 23 2023 14:03:00.000

null

'f'

'CII3'

'K'

Mon Jan 23 2023 14:04:00.000

null

'e'

'CII3'

'L'

Mon Jan 23 2023 14:05:00.000

null

null

Foreign Keys

CI.CASE_ID

EI.CASE_ID

CII.CASE_ID

EII.CASE_ID

Result

Column1 : string

Column2 : string

'D'

'F'

'E'

'H'

'G'

'K'

[5]

Example of LINK_FILTER_ORDERED with LINK_SOURCE as the filter expression and LINK_SOURCE/LINK_TARGET on two activity tables. The OUT columns of the activity tables are specified as outgoing edges of the corresponding tables, and the IN columns as incoming edge of the corresponding tables.

LINK_FILTER_ORDERED filters on edges whose outgoing link is part of case CI2, and their descendants. There are two outgoing links with case ID CI2, one link from activity D to activity F and another link from activity E to activity H of activity table EII. Since we use LINK_FILTER_ORDERED, the next outgoing link from activity G to activity K is ignored. So the result contains the edges D ➔ F and E ➔ H.

Query

Filter 

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

Column1 

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

Column2 

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

Input

Output

CI

CASE_ID : string

VALUE : int

'CI1'

5

'CI2'

3

CII

CASE_ID : string

VALUE : int

'CII1'

1

'CII2'

4

'CII3'

2

EI

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CI1'

'A'

Mon Jan 23 2023 14:00:00.000

'a'

null

'CI1'

'B'

Mon Jan 23 2023 14:01:00.000

'b'

'a'

'CI1'

'C'

Mon Jan 23 2023 14:02:00.000

null

null

'CI2'

'D'

Mon Jan 23 2023 14:03:00.000

'c'

'b'

'CI2'

'E'

Mon Jan 23 2023 14:04:00.000

'd'

null

'CI2'

'F'

Mon Jan 23 2023 14:05:00.000

null

'c'

EII

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CII1'

'G'

Mon Jan 23 2023 14:00:00.000

'e'

null

'CII1'

'H'

Mon Jan 23 2023 14:01:00.000

null

'd'

'CII2'

'I'

Mon Jan 23 2023 14:02:00.000

'f'

null

'CII2'

'J'

Mon Jan 23 2023 14:03:00.000

null

'f'

'CII3'

'K'

Mon Jan 23 2023 14:04:00.000

null

'e'

'CII3'

'L'

Mon Jan 23 2023 14:05:00.000

null

null

Foreign Keys

CI.CASE_ID

EI.CASE_ID

CII.CASE_ID

EII.CASE_ID

Result

Column1 : string

Column2 : string

'D'

'F'

'E'

'H'

[6]

Example of LINK_FILTER and CASE WHEN on case tables. The OUT columns of the tables are specified as outgoing edges of the corresponding tables, and the IN columns as incoming edges of the corresponding tables.

LINK_FILTER filters on case B1 and all descendants of it. The filter is propagated along the outgoing edges of B1. We use BIND_FILTERS to bind the result to table "CASE_C". This allows us to see which values occur after B1 in our Object Link graph.

Query

Column1 

         "CASE_C"."CASE_ID"

Column2 

         CASE WHEN BIND_FILTERS ( "CASE_C" , LINK_FILTER ( "CASE_B"."CASE_ID" = 'B1' , DESCENDANTS ) ) THEN 'Afterwards' ELSE NULL END

Input

Output

Case_A

CASE_ID : string

OUT : string

'A1'

'a'

'A2'

'b'

Case_B

CASE_ID : string

OUT : string

IN : string

'B1'

'c'

'a'

'B2'

null

'b'

Case_C

CASE_ID : string

IN : string

'C1'

'c'

'C2'

'c'

Result

Column1 : string

Column2 : string

'C1'

'Afterwards'

'C2'

'Afterwards'

[7]

Example of LINK_FILTER and LINK_SOURCE/LINK_TARGET on two activity tables. The OUT columns of the activity tables are specified as outgoing edges of the corresponding tables, and the IN columns as incoming edges of the corresponding tables.

LINK_FILTER filters on activity A and all descendants of activity A. The filter is mapped to the case containing activity A, case CI1. This case has two outgoing links, one from activity A to activity B and one from activity B to activity D. The corresponding case CI2 has two more outgoing links, one to activity F and another to activity H of activity table EII. At this point the hop limit of 2 is reached and therefore the next link from activity G of activity table EII (which has the same case as activity H) to activity K is not included in the result.

Query

Filter 

         FILTER LINK_FILTER ( "EI"."ACTIVITY" = 'A' , DESCENDANTS , 2 );

Column1 

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

Column2 

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

Input

Output

CI

CASE_ID : string

VALUE : int

'CI1'

5

'CI2'

3

CII

CASE_ID : string

VALUE : int

'CII1'

1

'CII2'

4

'CII3'

2

EI

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CI1'

'A'

Mon Jan 23 2023 14:00:00.000

'a'

null

'CI1'

'B'

Mon Jan 23 2023 14:01:00.000

'b'

'a'

'CI1'

'C'

Mon Jan 23 2023 14:02:00.000

null

null

'CI2'

'D'

Mon Jan 23 2023 14:03:00.000

'c'

'b'

'CI2'

'E'

Mon Jan 23 2023 14:04:00.000

'd'

null

'CI2'

'F'

Mon Jan 23 2023 14:05:00.000

null

'c'

EII

CASE_ID : string

ACTIVITY : string

TIMESTAMP : date

OUT : string

IN : string

'CII1'

'G'

Mon Jan 23 2023 14:00:00.000

'e'

null

'CII1'

'H'

Mon Jan 23 2023 14:01:00.000

null

'd'

'CII2'

'I'

Mon Jan 23 2023 14:02:00.000

'f'

null

'CII2'

'J'

Mon Jan 23 2023 14:03:00.000

null

'f'

'CII3'

'K'

Mon Jan 23 2023 14:04:00.000

null

'e'

'CII3'

'L'

Mon Jan 23 2023 14:05:00.000

null

null

Foreign Keys

CI.CASE_ID

EI.CASE_ID

CII.CASE_ID

EII.CASE_ID

Result

Column1 : string

Column2 : string

'A'

'B'

'B'

'D'

'D'

'F'

'E'

'H'