Skip to Content

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:

image

Loading the Qualified lookup table data:

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

image

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

image

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.

image

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.

image

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 –

image

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

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.

To report this post you need to login first.

6 Comments

You must be Logged on to comment or reply to a post.

  1. Basayya Kallimath

    Hi Pooja,

    Thank you for your valuable input about Lookup tables.
    I have similar situation in my project, where price of material to be picked based on Plant from MDM. Is it possible? After setting up all lookup tables do we need to do any enhancement in CATALOG_ENRICH_BADI? I have done all the configuration as mentioned above, still price is not changing according to plant. Kindly guide me to set up this scenario.

    Thanks,
    Regards,
    Basu

    (0) 

Leave a Reply