Qualified Tables Working

[Former Member]

Hi All,

How to Map the qualified fields in the qualified tables if the qualifier for that field is set to YES, because such fields are not reflected in the<b> import manager </b> for mapping only the fields whose qualifier is set to NO are visible.Thee problem is wen the qualifier for a field is set to YES you can feed the data only in the DATA MANAGER as these fields are only visible therewhich become so much of a lengthy process if data is large.

Can any one please explain the importance of the qualifier in the qualified table in layman terms, how to make the best use of it and please if possible give a short and sweet example for it.

HELP NEEDED URGENTLY

Thanxs in Advance.

[Former Member]

Hi Prasad,

<b>Where do we use the Qualified tables:</b>

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.

<b>Difference between Qualified lookup table and Flat lookup table:</b>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)

<b>Watch out for the definition of Qualifiers and non-Qualifiers:</b>

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.

<b>Our Scenario:</b>

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

<b>Observation:</b>

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.

<b>Design of repository:</b>

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’.

<b>Step1:</b>

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 only two fields (non qualifiers)can b seen 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.

<b>

Step2:</b>

Loading the Main table data:

Map all the source fields to corresponding fields in the repository.

Notes: Here 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

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.

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.

Please award me points if u find trhis blog as a value addition.

Thanks & Regards ,

Deepankar

[Former Member]

Hi ,there are two ways to import qualified tables:

1) first import to qualified table and then to main table ( dont forget to include ur qualified field in the main table):

first u select the sheet in source data sheet and ur qualified table in destination table then in map fields only no qualifier will be seen u have to map them and also value mapping for them and iafter record matching import it.after it u select the main table in ur destnation table and map fields accordingly and then value mapping ,record matching and import it.ur data u can see in data manager.

well this is a long method .

2) This is the most easiest method select ur sheet in source sheet and main table in ur destnation table.after it map the no qualifiers then go in destnation fields right click on the qualified field select the option set qualified update it will show ur all yes qualifiers in that select the first option as create and in second select update all mapped fields .after this do value mapping and record matching and import it ur data will be shown in ur mdm data manager. in qualified tables as well as in the qualified field in the main table.(in record mode)

[Former Member]

Hi Prasad - Let me explain a solution using an example..

Consider your master data as following (Names and data content for illustrative purpose only)

<b>ProdCd Description Region Price Currency</b>

AAA Laptop US 1000 USD

AAA Laptop Germany 800 EUR

BBB Desktop US 800 USD

BBB Desktop India 40000 INR

Your table design should be as follows

<b>Main Table :</b> Products

<b>Fields :</b> ProdCd , Description , Pricing ( Lookup- Qualified flat type)

<b>Qualified Table:</b> Pricing

<b>Fields :</b> Region (Non-Qualifier, Lookup- Flat, Display field) , Price(Qualifier), Currency (Qualifier,Lookup- Flat).

<b>Lookup Table :</b> Region

<b>Fields :</b>Region

<b>Lookup Table :</b> Currency

<b>Fields :</b> Currency.

• Use of lookup tables (Currency,Region) is not mandatory but is best practice to avoid redundency..!

After defining the repositiory in console, u need to follow the following order/steps to upload the data..(Considering an upload from excel).

Import look up table contents first..(Region and Currency tables).

Import data to the qualifier table...(Only Non-Qualifier field information is uploaded - Region)

Import data to Main table: During this, you need to map the master data Region from source to Pricing field on the Target structure and do necessary mapping on the field contents.(Generally Auto map option will do the job , since we have already uploaded the Non-qualifier data during qualified field update).

Dont forget to map the fields of curreny and Price which are visible during main table mapping.

System will store the data relationships more or less in a nested table format, where in your main table record for Product will have one field by name Pricing which inturn is a table containing Price and Currency for the product based on the Region.

Qualified tables are used to maintain 1:N relation ship for each main table record.

<b>Note :</b> Data for Non-Qualifier field on the qualified table is maintained at qualified table level ONLY and Qualifier field content is maintained at main table level.

This should solve ur problem.

Feel free to question me for any further clarification.

Cheers,

Aravind Nerella

[Former Member]

Hi Aravind,

The thing which i dint understand is that first ,

1) We map the source fields with the flat tables which are used as lookup tables i.e Currency and Region.

2) Next we map the non-qualifier fields which are in the qualified table , since the Region field of the source is already mapped how would it map again with the non qualified field Region of the qualified Table and wouldnt it cause some problem, similarly it holds for Currency.

3) What about the Price Field as it is Qualifier it cant be mapped in the import manager as it wouldnt be visible too.

4)You said that after doing the field level mapping are we again suppose to mapp the souce fields with the fields in the Main table i.e Pricing.......

[Former Member]

Hi Aravind,

As you said i created the same structure in the console and the same master data in Excel.I Mapped all the look up tables first i.e Currency And Region with fields currency and region.

Then i mapped the non qualifier field (Region)in the qualified flat table with the Region Source Field.

After that as you said i tried mapping the main table fields by doing AutoMap in which Currency, Price, ProductCD, Description got mappd to the respective fields in the destination with the same name.Currency field had to be again mapped cause it is a lookup.

The Region Field is gettin mapped to the Region Field in Destination which is a non qualifier in the qualified table Pricing.

While importing this particular Main Table Mapping i am gettin an error that "Map PRICING field to import qualifiers".

Please Help .......Urgently

[Former Member]

Prasad - You are almost there...:-)

NOTE: Just mapping in the order of Lookup, then N/Q fields on Qualified and then Main table will not help...you should import the data too...before proceeding with mapping the next level of tables...

One quick point.. You will not see the field "Region" when mapping the to the destination "Product" main table...but you should be able to see the "Pricing" field which will point to the qualified table "Pricing".

Then you should do "Value Conversion and Mapping" for the region from source to destination...you will be able to see the values for region on the destination side, if you have already uploaded the data for Region lookup table...

Once done , I belive you should be good to go..

I can send you screen shots..but couldn't on SDN..:-(

Any way..come back for any further details.

Cheers,

Aravind

[Former Member]

Hello Aravind,

I am experiencing the very same problem. I have attempted to import the fields in the order you described and I can now complete them all with no error. Your guidance was very helpful to me!

However, when I review the records of the main table in the Data Manager, there is no Price information. Could you please send me your helpful screen prints?

Many thanks and regards!

Meagan Tesdall

meagan.tesdall@gmail.com

[Former Member]

Hi Arvind,

The issue is that data is being sent in from another system like R/3 which contains all information as per your example - price,currency & product information.In the IM only the product & currency fields are available for mapping. The price field is availbale at the multi-valued lookup table level & it can be mapped- however after the data is loaded these price values are not present in the DM.

Is there any way to get around this ?

Regards,

Anita

[Former Member]

Hi - Please check if the fields on the multivalued lookup table (Price) are defined as display fields..If not please do that and you should be able to see them.

Even if it is not defined so, we can still see the fields when we have our control on the Q/Lookup in DM.

Let me know if it still doesnt work..

Cheers...

Aravind