Conditional Lookup Fields

An example would be adding a field from a dimension table to the fact table via a key field that joins the two tables.  

Adding a Conditional Lookup Field

Drag the field to be looked up (i.e. AccountNumber) from the source table to the destination table.

Depending on the existing joins and relations, the following options will be available when creating the lookup:

• If the source and destination tables do not have any relationships that have already been established between them, and there are no conditional lookup fields that have been added via a join between the two tables, then the Add Join window will appear so that the necessary join can be created. This is the scenario shown in the two examples above. See the Adding a Join to a Conditional Lookup Field section below for more information on adding a join and joins in general.
• If table relations or joins have already been established between the source and destination tables, then a different window will appear prompting the user to select whether to use an existing join or to create a new one, with the specific options available as follows:
  • Use default relation instead of joins: The conditional lookup field will use the default relation between the two tables as a join. This option is only available if a relation already exists between source and destination tables. The join icon will display as “read-only” when it is set to use the default relation.

    Warning: Use this option with care. The conditional lookup field will fail if you delete all relations between the source and destination tables, and may produce different values if the default relation is amended or changed in some way.

  • Add new joins: opens the Add Join window where a new join can be established for the lookup to use.
  • Copy joins from: creates a new join that is based on an existing relation or conditional lookup field.

The following two GIFs show the process adding a relation by first dragging the CustomerID field from the Customer table to the Order table.

After the field and its relation have been deployed in the Order table, then the conditional lookup field can be added.

Since the conditional lookup field is being added to a table that already has an existing relation, then the a window will appear with the following three options that are available for the join:

1. Use default relation instead of joins.
2. Add new joins.
3. Copy joins from.

Edit a Conditional Lookup Field

1. Right-click on the conditional lookup field and select Edit Conditional Lookup

   

2. The following menu is displayed

   

   1. In the Name field, enter a name for the lookup field.
   2. Select Use raw values to perform the lookup on the raw values in the source table, instead using the values from the Valid table, which would preclude any transformations and other cleansing tasks that might be performed on the data. Lookup values are always inserted into the raw destination table, and this setting does not affect that.
   3. Select Don't refresh data type to set the data type of the lookup field manually. By default, TimeXtender Data Integration will set the data type of the conditional lookup field to the data type of the first source field referenced by lookup. However, if the is more than one source field, then that data type may not always be correct. This option allows users to right-click the conditional lookup field and click Edit Data Type to amend the data type. 
   4. In the Multiple lookup fields list, select what value to be used when the new field contains more than one lookup field. The lookup fields are evaluated in the same order as they appear in the tree, and how a match is determined based on the selected evaluation setting:
      • 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.  

Adding Lookup Fields within a Conditional Lookup

Adding another Lookup Field to an existing Conditional Lookup

A conditional lookup field contains a lookup field for each source field that is a potential source for the eventual value of the conditional lookup field. Creating a new conditional lookup field will automatically add a new lookup field as shown below.

A conditional lookup field can look up values from more than one source field, and conditions can be configured to calculate which source value to use for the lookup value. As an example, a company may have may than one CRM system online at the same time. There is a new CRM system as well as a legacy CRM system that are both availalbe during the transition period In this scenario, the customer’s “AccountNumber” may be sourced from one of two different tables and there is a specified condition to ensure the appropriate value is selected.  

Use the following steps to add an additional lookup field to an existing conditional lookup. 

1. Expand the conditional lookup field, right-click Lookup Fields and click Add Lookup Field.

   

   Alternatively, drag a field from a table to Lookup Fields under the conditional lookup field. This pre-fills some of the settings in the Add Lookup Field window that appears.

   

   

2. In the Name field, type a name for the field.
3. In the Table list, select the table containing the field you want to use.
4. In the Field list, select the field you want to use.
5. In the Operator list, specify the type of aggregate function that will be used to scale a range of values into a single value, should this be needed. Select from one of the following aggregate function options:
   • Top: Returns the value from the first record that matches the join criteria. Selecting Top will also open a Sorting node for the list order, which can be important to ensure the appropriate value is at the top of the list. Right-click the Sorting node and click Add Sorting to define the sort order of the source table values.
   • Sum: Returns a sum of all the values that match the join criteria. This function only works 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 function only works on numeric values. Null values are ignored.
   • None
6. Click OK.
7. For the join that the lookup will use, select from one of the following three options and then click OK.  
   • Use default relation instead of joins.
   • Add new joins
   • Copy joins from   

The Lookup Fields section will now contain two source lookup fields. The next needed step is to add in conditions that will be used to determine which value is used as the source value, and the following section explains creating conditions in detail.

Copying a Lookup Field from one Conditional Lookup to another Conditional Lookup

To facilitate the addition of lookup fields, it is possible to copy an existing lookup field by dragging it from its current location to the Lookup Fields node of some other conditional lookup field.

Adding Conditions to Lookup Fields

Each lookup field can be configured with conditions that must first evaluate to true in order for the lookup field value to be used. Examples of lookup conditions:

• Only accept related values when a specified field in the destination table contains a certain value.
• Apply an aggregate function to scale a range of values.

Conditions must also be used to determine which lookup value to use when there are multiple source lookup fields. By default, the first lookup field where the condition evaluates to true will be used, and this can be the case even when the value returned is NULL, or no matching records are found. If no conditions are specified, then the first lookup field will always be used and any subsequent lookup fields will be ignored.

Use the following steps to add a condition to a lookup field. 

1. Expand the lookup field and click Conditions to open the Conditions task pane.
2. Select a Field in the Conditions pane on the right.
3. In the Operator list, select an function operator.
4. Either select Value and enter a value to use in the comparison in the box, or select Fields and select a field to use for the comparison in the list.
5. Click Add to add the condition.

Continuing the example above, where two lookups have been created for the AccountNumber (one from the Customer table and another from the NewCustomers table), conditions need to be added to each lookup field to determine when to use the value from that field. The CustomerIDs in the two CRM systems do not overlap, so conditions can be added to specify that the value will be sourced from the Old CRM Customer table for any CustomerIDs that are less than 1,000, and sourced from the New CRM Customers table for any CustomerIDs that are greater than or equal to 1,000. 

More complex conditions can be added using the custom condition operator that allows users to script the condition manually. For example, the above condition can be scripted out manually using the following steps:

1. Click on conditions under the lookup field
2. Select the name of the table in the conditions pane
3. Select Custom condition as the operator
4. Click Add
5. Enter the script for the condition
6. Click OK
7. Click Add to add the condition

Adding a Join to a Conditional Lookup Field

All conditional lookup fields require at least one join exist or be created between the source and destination tables, since the join is the means by which the lookup of the value is made. For example, when looking up the value of the “CustomerName” field in the Customers table, a join would need to be created that matches the CustomerID field that exists in both tables. 

To add a join to a lookup field, follow the steps below:

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

   

   

    

2. In the Join Column list, select the field from the source table to be used in the join for the lookup.
3. In the Operator field, specify how to relate the source join field, to either a join field in the destination table or a fixed value.
4. Select Field or Fixed Value as the Value Type to specify the destination side of the comparison. The Value box will update itself accordingly and a selection can be made as follows: 
   1. For “Field” value type, select the relevant field in the Value list.
   2. For “Fixed Value”, enter a value in the Value box.
5. Click OK to add the join and close the window, or +1 to create another join.

Adding a Conditional Lookup Field from a History Table

When creating a conditional lookup field from a history-enabled table, a condition must be configured that specifies the date to use when looking up the appropriate record. The following window will appear that prompts for this date condition on history-enabled tables.

The following options are available:

• Use this field: Select a date field on the destination table. The conditions will specify that the record should be the one valid on that date. Use this option when the conditional lookup should return the account number value at the time the order was created (OrderDate) or last modified (ModifiedDate). 

  

• Use the current record: the record that is marked as current will be the one selected. Use this option to return the account number that is current and most up-to-date.

  

• I will set up any conditions myself: TimeXtender Data Integration will not set up any conditions.