Sign Up for Training |
Jet Global Company Site
Community
Downloads
Submit a Request
Give Feedback

Adding a Conditional Lookup Field


Overview

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.

Creating the Lookup

  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

    add_lookup.png

    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. 

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.

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

  2. On the Add Lookup Field dialog...

     

    ... specify the following:

    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

      

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.

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

    conditions.png

    This will display the Conditions pane to the right of the JDM
  2. In the Conditions pane...

    conditions2.png

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

      


 

Was this article helpful?
0 out of 0 found this helpful
Have more questions? Submit a request

Comments