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, whileDESCENDANTS
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. | |||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||
|
[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. | |||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||
|
[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. | |||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||
|
[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. | |||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||
|
[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. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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. | ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
| ||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
[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. | |||||||||||||||||||||||||||||||||||||
| |||||||||||||||||||||||||||||||||||||
|