In TimeXtender, Relations are used to specify how two tables are related, most commonly to subsequently add a lookup. Relations are created by dragging a primary key field from the source table (One side) onto the related foreign key field on the destination table (Many side).
Conditional Lookups are how you join tables together in TimeXtender. Once the relation is in place, you can drag and drop any field from the One side table, onto the Many side table. This creates the conditional lookup field using the related fields as the join. Once the table is executed, the field will be populated using the desired logic.
A traditional SQL JOIN might result in more or less rows in a table, which can lead to undesired results. However, a TimeXtender Conditional Lookup will never affect the rows in a table and will only be populated if a resultant value is found in the source table. However, be sure your join criteria is unique. If not, the lookup will pull in any one of the possible values.
Rename Product Fields
Expand DSA > Tables > Product Model
Select the Name field and press F2 to rename: ProductModelName
Expand the Product Table
Select the Name field and press F2 to rename: ProductName
Lookup Product Model Name
Right-click Product Model
Click Open New Window
Expand the ProductModel table
Drag and drop ProductModelID to ProductModelID in the Product table
Click Yes to create the product relation
Add Conditional Lookup
Drag and drop ProductModelName to the Product table
Use default relation (created above) and click OK
Notice the Lookup and Relation created at the bottom of the field list
When Incremental Load is enabled in an ODX data source, Data Warehouse tables mapped from this source will inherit these settings automatically.
Identify if a table has Automatic incremental load by the little I on the icon of the table mapping.
OR Right-click on the table > Table Settings > Data Extraction, and see if Automatic is selected.
Why are my new fields null?
When Incremental Load is enabled, a newly created table will load all available rows. However, each subsequent execution will only load new or modified data. This means that newly added fields, conditional lookups or transformations will not show any new data until the table is executed using full load.
To execute a table using Full Load you can use one of the options below:
Deploy & Execute the Table
Click Review Tasks
Under Deployment steps, select
Full Load valid table
From now on
Right-click the table > Table Settings
Click the Data Extraction tab
Select Full Load
Keep Field Values up to Date
On tables that you wish to incrementally load, but have lookup values that may update on older records, you can enable Keep field values up-to-date to ensure those values are updated even when the table is incrementally loaded.