Discussion Board for collaboration on QlikView Scripting.
I want to create a model using these 4 tables.
Item(Master)(Common Field Item No)
WE(Transaction Tables)(Common Field=> Item No.Location Code,Bin Code,Lot No.)
ILE(Transaction Tables)(Common Field=> Item No,Location Code,Bin Code, Lot No. )
VE(Transaction Tables)(Common Field=> Item No,Location Code)
I want to show show qty and cost group by Item No,Location Code,Bin code.
Challenge is Common fields are different in four tables and synthetic keys are occurring during Loading.
LOAD [Item Category Code],
[Product Group Code],
(ooxml, embedded labels, table is ITEM);
(ooxml, embedded labels, table is WE);
LOAD [Entry No.],
(ooxml, embedded labels, table is ILE);
LOAD [Item No.],
[Cost Amount (Expected)],
[Cost Amount (Actual)],
[Item Ledger Entry No.],
[Item Ledger Entry Quantity]
(ooxml, embedded labels, table is VE);
Thanks in advance,
You can doing using a Link Table, but personally I'd prefer to concatenate all transaction tables into a single fact and restore missing associations with generic link tables.
I'm describing both approaches with a lot of detail in my book QlikView Your Business. Check it out if you'd like to learn advanced Data Modeling in QlikView.
which page in the book ? as i cant find a link table with different key combinations
Can someone do a link table with 2 ,3 , 4 key field combinations eg
product_id & customer_id
product_id & customer_id &supplier_id
product_id & customerid & supplier_id & debtor_id
so these are the common fields between the 3 tables
they each hav 20 other fields that are different in each table
I agree with Oleg. Use Concatenate statement to load all transactional tables to one fact table. This approach is much better then link table, especially with large datasets.
LOAD ...<necessary fields>...
Concatenate (FactTable) LOAD ...<necessary fields>...
Responding to your question:
- Link Tables are described on pages 431-457
- Concatenated Facts are described on pages 628-647 in the book
Upgrade your Qlik skills at the Masters Summit for Qlik - coming to Prague this April!