topic Re: Mapping (One- Many) Data modeling in QlikView

Mapping (One- Many) Data modeling

krish2459 — Sat, 16 Nov 2024 17:42:20 GMT

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

Re: Mapping (One- Many) Data modeling

krish2459 — Tue, 15 Dec 2020 09:31:19 GMT

Hi,

Any Ideas or thoughts to crack this scenario.

Thanks..

Re: Mapping (One- Many) Data modeling

marcus_sommer — Tue, 15 Dec 2020 10:41:40 GMT

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

Re: Mapping (One- Many) Data modeling

settu_periasamy — Tue, 15 Dec 2020 13:53:47 GMT

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;