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.
Final Qualified data in main table:
The data is available in the Data Manager as shown – 
P.S. If there is only one non-qualifier in a scenario, then the Compound field is not created and direct one to one mappings suffice.
