Conditional Lookups in the JDM

1. Expand the preferred business units and then expand the staging database.

2. Expand the tables with the staging database and select the table you wish to modify.

3. Right-click the table and then select Add Condition Lookup Field

4. Adding the Conditional Lookup Field

   

   1. In the Name field, type a name for the lookup field.

   2. 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.

   3. 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.

   4. 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

5. click OK.  The field is added to the project tree under the selected table. 

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.

1. Expand the field, and then right-click Lookup Fields. Click Add Lookup Field.

   

   1. Name -  type a name for the lookup field.

   2. Table - select the table containing the field you wish to use.

   3. Field - select the field you wish to use.

   4. Operator - 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 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.

   5. 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.

   6. Click OK

      On the Add Lookup Field dialog...

       

      ... specify the following:

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.

1. Expand the lookup field, right-click Joins, and then select Add Join.

2. On the Add Join dialog...

   

   ... specify the following:

   1. Join Column -  select the field that uses the lookup.

   2. Operator - specify when to look up a value.

   3. 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.

   4. Value - Depending on your chosen Value Type, click the relevant field in the Value list or enter a value in the Value box

   5. 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.

1. Fully expand the lookup field, click Conditions

   

   This will display the Conditions pane to the right of the JDM

2. In the Conditions pane...

   

   ... specify the following:

   1. In the upper section of the pane, select the field that you wish to use in the comparison.

   2. In the lower section of the pane, select the Operator you wish to use.

   3. 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.

   4. Click Add