5 Replies Latest reply: Sep 13, 2011 8:38 AM by wms RSS

    Linking the tables

      Hi Guys,

       

           I have a two tables called sales and purchase of the company and those two tables has [company name] and [item id] in common.

       

           How can I map those two tables and create some charts..?

       

           Can anyone help me in this..?

       

      Thanks

      Rajan.

        • Linking the tables
          Anand Chouhan

          Hi,

           

          As my understanding i suggest link this two table with  [item id] by this you are able to map this tables and able to make charts.

           

          Regards

          Anand

          • Linking the tables
            Martin Pohl

            Hi Rajan,

             

            the native QlikView priniciple is to join the tables by the two fields company name and item id by itself.

            Then you will get a synthetic key, which is not fine in QlikView.

            So you have to edit the loading script to

            [company code]&[item id] as ccii

            as a new key field in both tables.

            For a singel selection hold the company code and the item id in one table (prefer the table that includes all datas).

            So you will have one key field between both tables and can select all datas.

            Regards

            • Re: Linking the tables
              Anand Chouhan

              Hi

               

              I think you need to load some thing like this

               

              Qualify *;

              Unqualify CID;

               

              Sales:

              LOAD

                   [Company Name],

                   [Item Id],

                   [Company Name]&'-'&[Item Id] as CID

              FROM

              ..\Data.xlsx

              (ooxml, embedded labels, table is Sales);

               

               

              Purchase:

              LOAD

                   [Company Name],

                   [Item Id],

                   [Company Name]&'-'&[Item Id] as CID,

                   Value

              FROM

              ..\Data.xlsx

              (ooxml, embedded labels, table is Purchase);

               

               

              See the attached sample file.

               

              HTH

               

              Regards

              Anand

              • Re: Linking the tables
                Anand Chouhan

                Hi,

                 

                Another way you join this two table and make one table like

                 

                Sales:

                LOAD

                     [Company Name],

                     [Item Id]

                FROM

                Data

                 

                Purchase:

                Join(Sales)

                LOAD

                     [Company Name],

                     [Item Id],

                     Value

                FROM

                Data

                 

                HTH

                 

                Regards

                Anand

                • Re: Linking the tables

                  I guess it depends on what you want to graph.  The suggestions above will only link the companies that have both sold someting and purchased something and that is probably not what you want.

                   

                  One option is to do a concatenate load.

                   

                  FactTable:

                  Load

                   

                       'Sales' as RecordType, //this field could be used a dimension on your graph

                       [Company Name],

                       [Item Id],

                       SalesDate as TransactionDate, //Assuming you have a date there.  Creating a common date field

                       QTY as QtySold

                  FROM

                  SalesFacts

                   

                  Concatenate Load

                   

                       'Purchase' as RecordType,

                       [Company Name],

                       [Item Id],

                        PurchaseDate as TransactionDate,

                       Value as QtyPurchased,

                  FROM

                  PurchaseFacts

                   

                   

                   

                   

                  The resulting table will have all the companies and id's listed.

                   

                  Final Structure:

                   

                  RecordType, Company Name, Item Id, TransactionDate, QtySold,QtyPurchased