Look up/Qualified

[Former Member]

Guys,

I am facing a tricky situation here... As per my understanding we have the main table look up another flat table. When we say "look up", we are not maintaining any primary/foreign keys here but just selecting values, as we do from drop downs in many day to day applications... correct?

And we use qualified tables to have any kind of links between the main table and qualified tables.

What is the solution if we have an architecture to have 3 tables (1 main, 1 qualified and 1 flat) having primary/foreign keys to access/look up each of tose. Do we have something like this supported?

I know that we can have a scenario in which we have main table, then qual, and then flat wherein qual fetches values from flat table).

Apart from this, is it possible to "set" one of the look up values in main table as we do in qualified table? (Set links)... or user has to goto Data Manager and then select values himself?

[Former Member · Accepted solution]

Hi Devashish,

What is the solution if we have an architecture to have 3 tables (1 main, 1 qualified and 1 flat) having primary/foreign keys to access/look up each of tose. Do we have something like this supported?

Yes, it is definitely supported in MDM. We have it inn our repository the foll structure :

Main Table->Qualifier Table->Look-up Flat

A lookup field from the Main table will be pointing to the Qulaifer table and again a link from the Qualiifer table will be pointing to the Lookup Flat.

Apart from this, is it possible to "set" one of the look up values in main table as we do in qualified table? (Set links)... or user has to goto Data Manager and then select values himself?

If I have understood your problem correctly,

There can be 2 sol to this:

1.You can update,add or replace the links in case of the Qualifer Tables. You are given this option in Import Manager config options:

Set Qualifier Update - Add/Update/replace

Based on your requirement, you can definitely choose 1 out of them.

2. Also you can select any value for the link up field from the data manager. You can even perform the Mass-update for th e records and update/ replace/add any link to the link-up field.

Hope it helps.

*Please reward points if useful.

Thanks and Regards

Nitin Jain

[Former Member · Accepted solution]

Thanks Nitin/Cimona/ccr for the replies...

@Nitin: There is a slight deviation that my main table "partner id" is also having a reference maintained at the look up table. So my look up table architecturally comes between main table and qualified table... look table stores the keys from both main table and qual table and on basis of these keys, we fetch the values of more fields in look up...

Main Tbale: Partner ID

Flat (look up) Table: Partner ID, Qual_Unique_Key, field1, field2

Qualified Table: Qual_Unique_Key, Address

Now, what I want in main table to appear for the record is:

Partner Id 100, Address A1, Field1, Field2

Partner Id 100, Address A2, Field1, Field2

Is there a better way to do this or some workaround?

[Former Member · Accepted solution]

Hi Devashish,

I think your problem is a bit tricky.

Main Tbale: Partner ID

Flat (look up) Table: Partner ID, Qual_Unique_Key, field1, field2

Qualified Table: Qual_Unique_Key, Address

Now, what I want in main table to appear for the record is:

Partner Id 100, Address A1, Field1, Field2

Partner Id 100, Address A2, Field1, Field2

Solution - As far as my understanding, This is the situation

Main Table--

---

>Lookup flat--

---

>LookupQualifier

Product ID100 ProductID100, 1, Field1, Field2 1, Address 1

1, Address 2

Hence I feel the best feature that can help you out will be Joins, Pivoting, Reverse Pivoting in the IMport Manager.

You can join tables if they have a foreign ker relationship.

you can join flat and Qualifier table using 1.

Then the Main Table using ProductID100.

After this, if this is not sufficient, then try Pivoting. You can read about Joins and Pivoting from the Import Manager Reference Guide - Challenges.

Hope it helps.

*Please reward points if useful

Thanks and Regards

Nitin Jain

[Former Member]

Hi Bharti,

You are anlytics are right but it is not the way MDM works.

In MDM we have different tables created that work in different scenarios for different purpose.

If you wnat the Lookup falt tow ork in line swith the qualified tables (Set links) then it is difficult as it is not designed to achieve that.

But however there are many other differnt faetures in MDM is you understand them and use them correctly then you can meet your requirement.

Eg:Apart from this, is it possible to "set" one of the look up values in main table as we do in qualified table? (Set links)... or user has to goto Data Manager and then select values himself?

- Now for this req you cannot design the lookups to set up links with every main table record as Qualifeid table does.

As Lookup flat is a normal table having set of legal values which one has the facility to choose from the drop down.

However you can use Assignments here on your main table records in such a way that for a record ,if it has a certain field value you can assign a lookup value to it .Thus seting a link through assignments but this may not be dynamic.

like If you your pincode is 400708 then select city as Mumbai

Where Mumbai is actually a value from your lookup flat.

Hope it helps

Kindly reward points if found useful

Thanks

Simona

[Former Member]

Try not to think in relational database terms. The database schemas are hidden from the MDM users

"A lookup table is used to store values that are shared by many records in other tables, and also to act as a valid table that defines the set of legal values of the corresponding lookup field for data entry and search. Typical examples for lookup tables

are ”Plant” in the “Material” repository or “Partner Type” in the “Business Partner”

repository."

Recommended that you limit the number of records fields in a lookup table. E.G limit the number of look values is the thousands.

"A qualified table in MDM stores a set of lookup records, and also supports qualifiers that apply not to the qualified table record by itself, but rather to each association of a qualified table record with a main table record.

The limiting element in the usage of qualified lookup tables is the set of lookup records (this is what is considered the non-qualifier part), not the qualifier fields. Here the same principle as for the normal lookup tables can be applied – the overall number of lookup records should be limited"

Example:

Your qualified lookup records might be the combination of Plant and Inspection Type. You have specifiic values for the Inspection details like the inspection % and Max/Min Time etc. These specifics will qualify the qualified lookup recrod i.e. the Plant-Inspection Type (make it distinct)

You associate a Material in the main table to a Plant-Inspection Type (limited number of possible values here).

Rgs