Lookup fields are used to add a field to a table in order to retrieve the value of the field in another table. The process of adding a conditional lookup field consists of a number of steps described below.
- Expand Business Units, expand the preferred business units, and then expand the staging database.
- Expand Tables, and then select the table you wish to modify.
- Right-click the table, and then select Add Conditional Lookup Field.
- In the Name field, type a name for the lookup field.
- Select Force single lookup to overwrite the default behavior where multiple different conditional lookups from the same table with the same join and aggregation type is grouped together and retrieved in one SQL statement. When this option is selected, lookups will always be performed in separate update statements. This carriers a performance penalty and as such, you should only enable this option for troubleshooting purposes.
- 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.
- Select Manual index creation to prevent Jet Data Manager from automatically creating indexes on the source table on the fields specified in the join, with the lookup field(s) in the include clause. Enable this option if you wish create indexes manually on the source table.
- Select Override data type to set the data type of the lookup field manually. As a default, Jet Data Manager will set the data type of the conditional lookup field to the same data type as the source field of first lookupfield. If you enable this option, you can right-click the conditional lookup field, when it has been added, and click Edit Data Type. This option is useful if, for example, you are doing one conditional lookup on multiple fields with different data types that must have a target data type that is different than the data type of the first lookup field.
- Click OK. The field is added to the project tree under the 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.
The Add Lookup Field window appears.
- In the Name field, type a name for the field.
- In the Table list, select the table containing the field you wish to use.
- In the Field list, select the field you wish to use.
- In the Operator list, specify 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 a sum of all the values that match the join criteria. This will only work on numeric values. Null values are ignored. 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.
- (Optional) In the SQL Mode list, click the the mode you wish the generated SQL script to use. Usually, the default value will give you the best performance.
- Click OK.
Note: 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.
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.
- In the Join Column list, select the field that uses the lookup
- In the Operator field, specify when to look up a value.
- Click Field or Fixed Value to specify if you wish to compare the field selected in the join colum list to a field on the destination table or a fixed value. The Value box changes to fit your choice.
- 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 now specify conditions for when to lookup. The 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.
- Expand the lookup field, right-clickConditions, and click Add Condition.
- In the Field list, click the field that you wish to use in the comparison.
- In the Operator list, click the operator you wish to use.
- Click Value and enter a value to use in the comparison in the box
- OR -
Click Fields and select a field to use for the comparison in the list.
- Click OK to add the condition.