Configure Conditional Lookups in the Jet Data Manager
Overview
Lookup fields are used to add a field to a table in order to retrieve the value of the field in another table.
Creating the Lookup
Expand the preferred business units and then expand the staging database.
Expand the tables with the staging database and select the table you wish to modify.
Right-click the table and then select Add Condition Lookup Field.
-
Adding the Conditional Lookup Field
In the Name field, type a name for the lookup field.
Select Use raw values to perform the lookup on the raw values of the source table instead of the valid values (i.e. before any transformations or other cleansing tasks are performed). Lookups are always inserted into the raw destination table, and this setting does not affect that.
-
If Don't refresh data type is checked, the conditional lookup field's data type can only be changed manually - an no automatic refreshing of the data type will occur.
If this option is not checked (default), the data type will be synced with the first lookup field and refreshed when the first lookup field changes.
-
Multiple lookup fields controls what Jet Analytics will do when there is more than one lookup field on a conditional lookup field. The lookup fields are evaluated in the same order as they appear in the tree. What happens when there is a match depends on the setting which can be one of the following:
- Take the first value - The value of the conditional lookup field will be the value of the first lookup field with a condition that evaluates to true
- Take the first non-empty value - The value of the conditional lookup field will be the value of the first lookup field with a condition that evaluates to true and is not empty. This setting makes it easy to support a master data management (MDM) pattern where you have multiple candidates for a value and want the first that is available
Click OK The field is added to the project tree under the selected table.
Specifying the Lookup Field
The next step is to specify the lookup field that contains the values to be used in the field you just created. You can add multiple lookup fields to one conditional lookup field.
-
Expand the field, and then right-click Lookup Fields . Click Add Lookup Field .
In the Add Lookup Field window, provide the following:
Name: Enter a name for the lookup field.
Table: S elect the table containing the field you wish to use.
Field: S elect the field you wish to use.
-
Operator: S pecify how to return the values. You have the following options:
Option Description Top Returns the value from the first record that matches the join criteria. When you select this operator, a Sorting node will be added to the project tree under the lookup field. Right click this and click Add Sorting to define how the matching values are sorted before they are retrieved from the source table.
Sum Returns the value from the first record that matches the join criteria. When you select this operator, a Sorting node will be added to the project tree under the lookup field. Right click this and click Add Sorting to define how the matching values are sorted before they are retrieved from the source table.
Count Returns a count of all the values that match the join criteria. Null values are ignored.
Maximum Returns the highest value of the values that match the join criteria. For strings, it will find the highest value in the collating sequence. Null values are ignored.
Minimum Returns the lowest value of the values that match the join criteria. For strings, it will find the lowest value in the collating sequence. Null values are ignored.
Average Returns the average value of the values that matches the join criteria. This will only work on numeric values. Null values are ignored.
SQL Mode (optional)- click the the mode you wish the generated SQL script to use. Usually, the default value will give you the best performance.
Click OK.
Dragging and Dropping the Lookup Field
You can also drag a field from one table and drop it on the name of another table. This will automatically create the Conditional Lookup field with the exception of the joins, which are covered below.
Adding Joins
Next you have to add a join that specifies which join criteria must be met in the source table. Less complex joins will make the lookup perform faster. Complexity is a combination of the number of fields in the join and the data type. To get the best performance, use one single numeric field for the join.
Expand the lookup field, right-click Joins , and then select Add Join
-
On the Add Join dialog, specify the following:
Join Column: Se lect the field that uses the lookup
Operator: S pecify when to look up a value.
Value Type: Click Field or Fixed Value to specify if you wish to compare the field selected in the join column list to a field on the destination table or a fixed value. The Value box changes to fit your choice.
Value: Depending on your chosen Value Type, click the relevant field in the Value list or enter a value in the Value box
Click OK
You can also drag a field from one table and drop it on the name of another table. This will automatically create the Conditional Lookup field with the exception of the joins, which are covered below.
Specifying Conditions
You can now specify conditions for when to lookupThe lookup will only be performed when the condition evaluates to true. For example, if you can determine that the lookup will only find related values when a certain field in the destination table has a certain value, apply a condition to avoid the lookup being performed on many records without finding a matching record. Conditions must also be used when having multiple lookup fields within one conditional lookup field to determine which lookup field to use. The first lookup field where the condition evaluates to true will be used, even if it returns a NULL value or finds no matching records. If no conditions are specified, the first lookup field will always be used and any subsequent lookup fields will be ignored.
-
Fully expand the lookup field, click Conditions .
This will display the Conditions pane to the right of the JDM.
-
In the Conditionspane, specify the following:
In the upper section of the pane, select the field that you wish to use in the comparison.
In the lower section of the pane, select the Operator you wish to use.
-
Value or Fields - Click Value and enter a value to use in the comparison in the box
or
Click Fields and select a field from the list to use for the comparison.
Click Add.