Taming the animal - Qualified Tables

Former Member · ‎03-29-2006

Where do we use the Qualified tables:

At times data is stored in such a way that duplication is unavoidable due to the storing mechanism and other factors. It may also happen that the data is sparse. The efficient way of storing data in such scenarios is the use of Qualified tables as it reduces the size of the main table and removes the unnecessarily created duplicates.

Difference between Qualified lookup table and Flat lookup table:

Flat lookup tables normally work on only a single field. It contains the set of legal values to which corresponding lookup field in the main table is assigned.

E.g. suppose we have a flat lookup table for Material Group for an automobile manufacturing company. Here we have a fixed set of legal values that can be looked up into the main table (for example Cars, Bikes, SUVs, etc.)

Qualified tables work on the combination of two types of fields where value of one or more fields (qualifiers) changes depending on the values of one or more other fields (non-qualifiers).

E.g. suppose we have a field “Price” whose values varies with the region for the same product. Here we have a fixed set of related legal values (combination of fields) that can be looked up into the main table. (For example Product A has a price of $30 in Southern region whereas $40 in Central region, then we get a set like Product A | Southern | $30, Product A | Central | $40 in the Lookup Qualified table)

Watch out for the definition of Qualifiers and non-Qualifiers:

This was something which took a long time for me to hunt down.
Qualifiers – are those fields whose values change based on the value of some other field(s) and whose value is different for each main table record
Non Qualifiers – These fields are only the part of qualified lookup table, but they apply not only to the qualified table but also to each association of the qualified table record to the main table record. Simply speaking, they are the fields that will decide the values in the Qualifier (main table)fields.

Our Scenario:

Suppose we have been provided with the following source data –

Manufacturer	Name of Drug	Region	Price	Storage Temp
A	Crocin	North	280	27 Celsius
A	Crocin	South	300	14 Celsius
A	Crocin	Central	260	30 Celsius
A	Disprin	North	380	30 Celsius
A	Disprin	South	360	27 Celsius
A	Disprin	Central	320	14 Celsius

Observation:

Values of ‘Price’ and the ‘Storage Temp’ field change based upon the ‘Name of Drug’ and ‘Region’ field values and also their values are different for each main table record; hence these should be madequalifiers in the qualified lookup table
Since ‘Name of Drug’ and ‘Region’ fields are deciding the qualifier values hence these should be kept as non qualifiers in the qualified lookup table. ‘Manufacturer’ and ‘Price’ will be part of the main table.

Design of repository:

We will discuss only the design of the main table ‘Products’ and the Qualified lookup table ‘Prices’ as they are of our immediate concern.

Products

Field	Field Detail
Manufacturer	Name- Manufacturer Type - Text Required – Yes Display – Yes
Lookup[Price]	Name- Lookup[Price] Type - Lookup[Qualified Flat] (multi-valued) Required – None Display – No Multi-Valued - Yes Lookup Table – Prices

Prices

Field	Field Detail
Name of Drug	Name- Name of Drug Type - Text Required – No Display – Yes Qualifier - No
Region	Name- Region Type - Text Required – No Display – Yes Qualifier - No
Price	Name- Price Type - Currency Required – No Display – Yes Symbol - $ Decimal Place - 0 Qualifier - Yes Cache - Yes
Storage Temp	Name- Storage Temp Type - Measurement Required – No Display – Yes Dimension – Temperature Default Unit – Celsius Qualifier - Yes Cache - Yes

Note: Caching of qualifiers is strongly recommended as it dramatically improves search performance.
Once the design of the repository is through, we now move on to the trickiest area – the one of the Import Manager

Importing records:

As per the standard practice, we load the lookup table first, here ‘Prices’ and then the main table ‘Products’.
The source preview is as shown:

Loading the Qualified lookup table data:

Select the corresponding tables in the source and destination hierarchy in the Import manager.

Go to Map Fields/Values tab and Map the corresponding fields in the field mapping grid.

Note: Here we can see only two fields (non qualifiers) in the destination field grid, however in the repository design we have created four fields.

The qualifiers (fields) never appear in the destination field of the qualified lookup table in the Import Manager.

Go to Match Records tab and select the non-qualifiers as matching field and Add it as Combination.

Select Import Action as Create as it is an Initial load of data.

Loading the Main table data:
Map all the source fields to corresponding fields in the repository.

Notes: Here we see many fields in destination (including qualifiers (Q) which are defined in the qualified table and not in the main table of the repository structure in Console).
Minimum required field mapping: All non-qualifier fields of the qualified lookup table should be mapped. Qualifier field (Q) is optional.
Now we are left with one field (Lookup [Price]) in destination which is still unmapped and we have no corresponding field left in the source to map it with. For this, go to source field tab and right click to create compound field as shown –

Lookup [Price] will be available in the source field and will get automatically mapped to the corresponding destination field.
Note: If any one of the non-qualifier fields of the qualified lookup table is not yet mapped, we are not allowed to create the compound field.
Do the Record Matching and Import the data into Data Manager Client by selecting the appropriate Import action.