Lookup expressions

Introduction

Lookup expressions have some similar elements as Aggregate expressions but also have some new elements. Lookup expressions can be seen as an implicit join with an input table. A Lookup expression enables you to access all attributes from the selected input table.

Note: In case you want to add a lot of items from a specific table, it is recommended to join both tables instead of creating lookups on many attributes.

Creating a Lookup

Follow these steps to create a Lookup.

Step	Action
1	Create a new expression.
2	Change its Type to Lookup and select the desired input table.
3	Set the expression level as needed.
4	Right-click in the lookup column, next to the Settings column, and select Add attributes… to add a lookup attribute.
5	Enter the lookup expression in the Expression field.
6	Click on OK to save the Lookup expression.

If you want to use an attribute in a table that is not available in that table, you can create a lookup to a table in which the attribute is available. You create the lookup by using an attribute that is available in both the current table as the input table.

Example

If you want to use the Invoice owner in the Events table, you must create a lookup to the Invoice table since the attribute is only available in the Invoices table. To get the information to the Events table, a lookup is created. To find the proper Invoice owner per event, the Invoice number which is available in both tables is used.

See the illustration below.

This expression adds a Lookup expression attribute to the Events table which stores the Invoice owner of each event.

Lookups and Expression Levels

The Settings and the Lookup columns are used to define how the Lookup table is connected to the Input table. The datasource attributes listed in both columns need to have the same values and must be placed in the same order. In the example above, Invoice number is added in both columns since it is the shared key between the Lookup table and the Input table.

All attributes listed above the expression level must be included in the Lookup column since they are needed to join both tables. Attributes below the expression level do not need to be shared by both tables.

Any attribute from the Input table can be placed in the Settings column below the expression level, in case it is needed for the lookup definition. In the example below, the Event end attribute is used to sort the records.

Below is an example of a lookup in the Case_preprocessing table that counts the distinct number of people who work on an invoice.

Note: For the Lookup expression itself, you only have attributes of the Input table available. If you need to make a calculation that requires attributes from both tables used in the lookup, a separate aggregate is necessary.

Globals Table

Lookups can also be used in the Globals table to get information from other tables that can be aggregated in one value. For example, to know whether an attribute is filled. See the illustration below for an example.

In this Lookup expression it is checked whether the Event end attribute has a value or not. No shared attributes are needed since all records of the table are accessed to handle them all together.