6 Replies Latest reply: Jan 27, 2016 2:22 PM by Gysbert Wassenaar RSS
      • Re: Removing synthetic Key from the Data model below
        Oleg Troyansky

        Hi Asma,

         

        I can't explain everything in a single community post, but I can recommend to you my new book QlikView Your Business. In the book, I explain all the data modelling techniques that are required here (and a lot more), and I even describe the analytical solution that's very similar to yours - Inventory Analysis.

         

        Cheers,

        Oleg Troyansky

        Upgrade your Qlik skills at the Masters Summit for Qlik - coming soon to Milan, Italy!

        • Re: Removing synthetic Key from the Data model below
          Gysbert Wassenaar

          There are several possible solutions. If the sales/purchase transactions are not directly related to inventory transaction you could simply concatenate the two tables and add a field for the transaction type. If a sales/purchase transaction must be directly related to inventory transactions you can create a link table.

           

          1. Create a field in both fact tables: autonumber(TransactionDate & '|' & masterid & '|'& warehouse) as LinkKey.

           

          2. Create the link table

           

          LinkTable:

          LOAD DISTINCT LinkKey, TransactionDate, masterid, warehouse FROM DailyInventory;

          CONCATENTAT(LinkTable)

          LOAD DISTINCT LinkKey, TransactionDate, masterid, warehouse FROM SalesAndPurchases;

           

          3. Drop the TransactionDate, masterid and warehouse fields from the two fact tables:

           

          4. Once you understand how and why this works use the Qlik Components Library to create that necessary link table for you.