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 object-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 an object in the graph. The graph structure is represented by an edge table of directed links between rows from the tables. These links can be defined via dedicated mapping tables, in which each row correspond to one link.

The LINK_FILTER operator can be used to filter along the links 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 objects of the graph that fulfill the given condition. All objects 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 objects and then extends the resulting filter to cover all preceding or succeeding objects (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 object and all ancestors of that object in the Object Link graph, while DESCENDANTS filters on the object and all descendants of that object.

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

NULL handling

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

Examples

[1]

A LINK_FILTER filter that emits all preceding objects.

Query

Filter 

         FILTER LINK_FILTER ( "MATERIAL"."ID" = 'Dough' , ANCESTORS );

Column1 

         "MATERIAL"."ID"

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

'Olive oil'

'Flour'

'Chicken'

'Egg'

'Dough'

[2]

A LINK_FILTER filter that emits all succeeding objects.

Query

Filter 

         FILTER LINK_FILTER ( "MATERIAL"."ID" = 'Dough' , DESCENDANTS );

Column1 

         "MATERIAL"."ID"

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

'Dough'

'Pasta'

'Veggie pasta'

'Chicken pasta'

[3]

LINK_FILTER can also be used in a CASE WHEN context as it is returning Boolean values. Since LINK_FILTER is working on link level, we should always use LINK_SOURCE/TARGET/ATTRIBUTES in this context in order to have the correct common table.

Query

Column1 

         CASE WHEN LINK_FILTER ( "MATERIAL"."ID" = 'Dough' , DESCENDANTS ) THEN LINK_SOURCE ( "MATERIAL"."ID" ) || ' -> ' || LINK_TARGET ( "MATERIAL"."ID" ) END

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

null

null

null

null

null

null

null

null

null

'Dough -> Dough'

'Dough -> Pasta'

'Pasta -> Veggie pasta'

'Pasta -> Chicken pasta'

[4]

A LINK_FILTER filter that emits all links between succeeding objects that are one hop away. Note that the information of having two "boil" links to the "Hard boiled eggs" object is persisted.

Query

Filter 

         FILTER LINK_FILTER ( "MATERIAL"."ID" = 'Egg' , DESCENDANTS , 1 );

Column1 

         LINK_SOURCE ( "MATERIAL"."ID" )

Column2 

         LINK_TARGET ( "MATERIAL"."ID" )

Column3 

         LINK_ATTRIBUTES ( "ACTION"."DESCRIPTION" )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

Column2 : string

Column3 : string

'Egg'

'Dough'

'mix'

'Egg'

'Chicken'

'hatch'

'Egg'

'Hard boiled eggs'

'boil'

'Egg'

'Hard boiled eggs'

'boil'

[5]

A LINK_FILTER filter that emits all links between preceding objects. Note that in this example links are contained in multiple mapping tables and that the object tables "MATERIAL" and "CUSTOMER" must neither be joined directly nor indirectly.

Query

Filter 

         FILTER LINK_FILTER ( "CUSTOMER"."ID" = 'Charlie' , ANCESTORS );

Column1 

         LINK_SOURCE ( "MATERIAL"."ID" )

Column2 

         LINK_TARGET ( "MATERIAL"."ID" , "CUSTOMER"."ID" )

Column3 

         LINK_ATTRIBUTES ( "ACTION"."DESCRIPTION" )

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

CUSTOMER

ID : string

LOCATION : string

'Alice'

'Munich'

'Bob'

'Amsterdam'

'Charlie'

'New York'

DELIVERY

OUT : string

IN : string

MODE : string

'Veggie pasta'

'Alice'

'standard'

'Chicken pasta'

'Bob'

'premium'

'Veggie pasta'

'Bob'

'premium'

'Hard boiled eggs'

'Charlie'

'standard'

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Result

Column1 : string

Column2 : string

Column3 : string

'Chicken'

'Egg'

'lay'

'Egg'

'Chicken'

'hatch'

'Egg'

'Hard boiled eggs'

'boil'

'Egg'

'Hard boiled eggs'

'boil'

'Hard boiled eggs'

'Charlie'

null

[6]

If a given attribute is not provided in the object table itself, we can utilize BIND_FILTERS to propagate the filter to the necessary table. In this example we only want to view materials for customers based in North America.

Query

Filter 

         FILTER LINK_FILTER ( BIND_FILTERS ( "CUSTOMER" , "CITY"."CONTINENT" = 'North America' ) , ANCESTORS );

Column1 

         "MATERIAL"."ID"

Input

Output

ACTION

OUT : string

IN : string

DESCRIPTION : string

DURATION : int

'Olive oil'

'Dough'

'mix'

3

'Flour'

'Dough'

'mix'

3

'Egg'

'Dough'

'mix'

3

'Vegetables'

'Veggie pasta'

'steam'

10

'Chicken'

'Chicken pasta'

'roast'

25

'Chicken'

'Egg'

'lay'

null

'Egg'

'Chicken'

'hatch'

null

'Egg'

'Hard boiled eggs'

'boil'

7

'Egg'

'Hard boiled eggs'

'boil'

8

'Dough'

'Dough'

'knead'

5

'Dough'

'Pasta'

'rest & shape'

30

'Pasta'

'Veggie pasta'

'cook'

2

'Pasta'

'Chicken pasta'

'cook'

2

CITY

NAME : string

CONTINENT : string

'New York'

'North America'

'Amsterdam'

'Europe'

'Munich'

'Europe'

CUSTOMER

ID : string

LOCATION : string

'Alice'

'Munich'

'Bob'

'Amsterdam'

'Charlie'

'New York'

DELIVERY

OUT : string

IN : string

MODE : string

'Veggie pasta'

'Alice'

'standard'

'Chicken pasta'

'Bob'

'premium'

'Veggie pasta'

'Bob'

'premium'

'Hard boiled eggs'

'Charlie'

'standard'

MATERIAL

ID : string

CATEGORY : string

QUANTITY : float

UNIT : string

PRICE : float

'Coal'

null

10.0

'kg'

1.0

'Olive oil'

'raw ingredients'

2.0

'tbsp'

0.08

'Flour'

'raw ingredients'

250.0

'g'

0.1

'Vegetables'

'raw ingredients'

500.0

'g'

2.3

'Chicken'

'raw ingredients'

1.0

'lbs'

6.0

'Egg'

'raw ingredients'

1.0

null

0.5

'Dough'

'intermediate goods'

400.0

'g'

1.0

'Pasta'

'intermediate goods'

400.0

'g'

3.5

'Veggie pasta'

'finished meals'

800.0

'g'

8.5

'Chicken pasta'

'finished meals'

900.0

'g'

9.5

'Hard boiled eggs'

'finished meals'

2.0

null

2.0

'Salad'

'finished meals'

500.0

'g'

6.5

Foreign Keys

CITY.NAME

CUSTOMER.LOCATION

Result

Column1 : string

'Chicken'

'Egg'

'Hard boiled eggs'

Signal Link (Deprecated)

Warning

The legacy Signal Link configuration is deprecated and no longer actively supported.

[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 links of the corresponding tables, and the IN columns as incoming links 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 links 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'

[8]

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

LINK_FILTER filters on case C2 and all ancestors of it. The filter is propagated along the incoming links 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 links 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'

[9]

Example of LINK_FILTER and LINK_SOURCE/LINK_TARGET on two activity tables. The OUT columns of the activity tables are specified as outgoing links of the corresponding tables, and the IN columns as incoming links 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 links 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'

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

[10]

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 links of the corresponding tables, and the IN columns as incoming links 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 links 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'

[11]

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 links of the corresponding tables, and the IN columns as incoming links of the corresponding tables.

LINK_FILTER_ORDERED filters on activities 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 links 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'

See also: