4 Replies Latest reply: Jan 11, 2018 12:05 PM by Oleg Troyansky RSS

    Link Table with different keys

      Hi Experts,

      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.

       

      Please Advice,

      Item:

      LOAD [Item Category Code],

           [Item No.],

           Description,

           [Product Group Code],

           [Unit Cost]

      FROM

      [C:\Users\Desktop\Test data Excel.xlsx]

      (ooxml, embedded labels, table is ITEM);

      WE:

      LOAD

      [Location Code],

           [Bin Code],

           [Item No.],

           [Lot No.],

           Quantity,

           [Expiration Date]

      FROM

      [C:\Users\Test data Excel.xlsx]

      (ooxml, embedded labels, table is WE);

      ILE:

      LOAD [Entry No.],

           [Bin Code],

           [Location Code],

           [Item No.],

           [Lot No.],

           [Manufacturing Date]

      FROM

      [C:\Users\Test data Excel.xlsx]

      (ooxml, embedded labels, table is ILE);

      VE:

      LOAD [Item No.],

           [Cost Amount (Expected)],

           [Cost Amount (Actual)],

           [Location Code],

           [Item Ledger Entry No.],

           [Item Ledger Entry Quantity]

      FROM

      [C:\Users\\Test data Excel.xlsx]

      (ooxml, embedded labels, table is VE);

       

      Thanks in advance,

        • Re: Link Table with different keys
          Oleg Troyansky

          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.

           

          cheers,

          Oleg Troyansky

          • Re: Link Table with different keys
            Radovan Oresky

            Hi,

             

            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.

             

            FactTable:

            LOAD ...<necessary fields>...

            FROM [C:\Users\Test data Excel.xlsx]

            (ooxml, embedded labels, table is WE);

             

            Concatenate (FactTable) LOAD ...<necessary fields>...

            FROM [C:\Users\Test data Excel.xlsx]

            (ooxml, embedded labels, table is ILE);

             

            Concatenate (FactTable) LOAD ...<necessary fields>...

            FROM [C:\Users\\Test data Excel.xlsx]

            (ooxml, embedded labels, table is VE);