Mapping (One- Many) Data modeling

[krish2459]

Hi,

We have two tables (Valuation Table and Stock Table)

Valuation table is at Material – Plant level

Stock Table is at Material – Plant – Storage Location (SLOC) Level

Problem Description

• No Storage Location in Valuation Table
• No Value in Storage Location Stock Table

Here We need to bring “Total Val.” In ‘Valuation Table’ to the ‘Stock Table’ at Material – Plant – Storage Location level.

Attached is the sample data.

Thanks . .

[krish2459]

Hi,

Any Ideas or thoughts to crack this scenario.

Thanks..

[marcus_sommer]

If there are just 1:n data the tables could for the most views be simply associated within the datamodel. But if there are missing keys on both sides there is no direct possibility to map/join/associate the tables - at least not if you always want to show all of them.

In many scenarios the easiest way to handle such cases is to concatenate (in sql = union) the tables. That the tables have a more or less different data-structure is no general showstopper whereby you may need to prepare the tables in some way, like adding missing data from elsewhere ...

More commonly but not easier and by large datasets with a worse performance is to create a link-table between your tables.

Another way - more expensive as the both mentioned approaches above - is to identify the missing keys on each table and adding them there directly per concatenate.

- Marcus  

[settu_periasamy · Accepted solution]

Hi,

Maybe try to join the valuation table into Stock Table based on Material and Plant. Then, divide the Total value by stock.

Stock:
 LOAD
         Material &'_'&Plnt     as      %MatPlnt,
         Material,
     Plnt,
     SLoc,
     Year,
     Pe,
     TotalStock
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, table is [Stock Table]);

Left Join(Stock)

Valuation:
LOAD
         Material &'_'& Plnt    as      %MatPlnt,
//      Material,
//     Plnt,
//     TotalStock,
     [Total Val.] / TotalStock   as EachVal,
     Pr.,
     MvAvgPrice,
     [Std price]
FROM
[Sample Data.xlsx]
(ooxml, embedded labels, table is [Valuation Table]);

Final:
LOAD   
        %MatPlnt,
         Material,
     Plnt,
     SLoc,
     Year,
     Pe,
     TotalStock,
     EachVal * TotalStock       as TotalValue,
      Pr.,
     MvAvgPrice,
     [Std price]
Resident Stock;

DROP Table Stock;