NIM

Relation items

In Filters, a new-relation-filter-item.pngRelation filter item joins two input tables to produce an output table. It is comparable to a SQL Join.

This is the most important type of filter item to understand, but also the most complicated. The most important points are:

  • In each relation item, the available input tables depend on your configured Intra-system relations and Inter-system relations.

  • The output table always includes all columns from the first table plus all columns from the second table. However, the same is not true of rows. The included rows are determined by the relation item's logical operator.

Thus, an output table may have more or fewer rows than either of the input tables. It depends on the order of the joined tables, the directionality of the 1-N intra-system relations between the tables, and the chosen logical operator.

For example (referring to the HR500 test dataset), say you have an employees table and an employments table. Each employee has one or more employments, i.e., a 1-N relation. Joining the employees table to the employments table with the any operator will produce a table with more rows than the employees table. Conversely, joining the employments table to the employees table with the any operator will produce a table with the same number of rows as the employments table. Changing the operator from any to—for example, multiple—will produce a table with fewer rows.

2021-05-18_15-26-11.png

The following logical operators are available:

Any

Include all rows in the first table, plus all rows in the second table which match at least one row in the first table.

No

Include all rows in the first table which do not match any rows in the second table.

Multiple

Include all rows in the first table which match at least two rows in the second table.

All

Include all rows in the first table which match every row in the second table.

Any-None

Include all rows returned by the Any condition, plus all rows returned by the No condition.