8 Replies Latest reply: Aug 20, 2015 9:56 AM by arjun rao RSS

    Joining table on multiple keys - how to

    Jeff Robertz

      Hi all,

       

      I am trying to expand my script capabilities with joining tables.

      I am a real dummy here and would appreciate guidance on the following case.

      I have two tables:

      Table 1: A table giving the actual sales by country, product and year

      Table 2: A table giving the potential sales by country, by product (not year as potential is market size) and the sales forecast by country, by product and by year

      My goal is to create a join in script so I get the table OUTPUT with all data (actual sales, potential sales and sales forecast) assembled that I can then easily use to make charts.

      Can anyone help me come up with the syntax?

      Getting this would help me understand how to join and help me progress further in creating QlikSense apps :-)

      I also attach an Excel file to make it easier for you to help me.

       

      Thanks in advance!

       

      Table 1:

               

      CountryCodeProductNameClientIDClientNameBilling DateCurr.TotalBillValueBill. Qty in SKUSalesMan
      AUProduct1100ABC10/03/2015EUR1,000.001John
      AUProduct2100ABC10/03/2015EUR100.001John
      AUProduct1101EFG10/03/2015EUR1,000.001John
      AUProduct2101EFG10/03/2015EUR100.001John
      ATProduct1102HIJ10/03/2015EUR2,000.002Henry
      ATProduct2102HIJ10/03/2015EUR300.003Henry
      BAProduct1103KLM10/03/2015EUR5,000.005Henry
      BAProduct2103KLM10/03/2015EUR200.002

      Henry

      Table2

            

      CountryCodeCountryNameProductNameCountryPotentialBillingYearSalesForecast
      AUAustraliaProduct130201512
      AUAustraliaProduct130201615
      AUAustraliaProduct220201512
      AUAustraliaProduct220201615
      ATAustriaProduct12420158
      ATAustriaProduct124201610
      BABosnia and HerzegovinaProduct2320151
      BABosnia and HerzegovinaProduct232016

      2

       

      OUTPUT (wished)

                 

      CountryCodeProductNameClientIDClientNameBilling DateCurr.TotalBillValueBill. Qty in SKUSalesManBillingYearCountryPotentialSalesForecast
      AUProduct1100ABC10/03/2015EUR1,000.001John20153012
      AUProduct2100ABC10/03/2015EUR100.001John20153015
      AUProduct1101EFG10/03/2015EUR1,000.001John20152012
      AUProduct2101EFG10/03/2015EUR100.001John20152015
      ATProduct1102HIJ10/03/2015EUR2,000.002Henry2015248
      ATProduct2102HIJ10/03/2015EUR300.003Henry20152410
      BAProduct1103KLM10/03/2015EUR5,000.005Henry201531
      BAProduct2103KLM10/03/2015EUR200.002henry201532
        • Re: Joining table on multiple keys - how to
          Srikanth P

          You can simply use Join (Left, Inner, Outer ,Right -- Optional) key word to join the tables and make sure that the key fields must be same name. Please find below:

           

          DATA:

          LOAD

            CountryCode,

          ProductName,

            ClientID,

            ClientName,

            [Billing Date],

            [Curr.],

            TotalBillValue,

            [Bill. Qty in SKU],

            SalesMan

          From Excelfile;

           

          Join(DATA)

          LOAD

          CountryCode,

            CountryName,

            ProductName,

            CountryPotential,

            BillingYear,

            SalesForecast

          From Excelfile;

           

          In the above example the tables joined on CountryCode & ProductName.

           

          Please go to help file to find out more on the Join

          • Re: Joining table on multiple keys - how to
            arjun rao

            Hi,

            Please use the below code and check the attached sample file.

             

            Thank you.

             

            Table1:

                   LOAD * INLINE [

            CountryCode,ProductName,ClientID,ClientName,Billing Date,Curr.,TotalBillValue,Bill. Qty in SKU,SalesMan

            AU,Product1,100,ABC,10/03/2015,EUR,1,000.00,1,John

            AU,Product2,100,ABC,10/03/2015,EUR,100.00,1,John

            AU,Product1,101,EFG,10/03/2015,EUR,1,000.00,1,John

            AU,Product2,101,EFG,10/03/2015,EUR,100.00,1,John

            AT,Product1,102,HIJ,10/03/2015,EUR,2,000.00,2,Henry

            AT,Product2,102,HIJ,10/03/2015,EUR,300.00,3,Henry

            BA,Product1,103,KLM,10/03/2015,EUR,5,000.00,5,Henry

            BA,Product2,103,KLM,10/03/2015,EUR,200.00,2,

            Henry

            ];

             

            JOIN (Table1)

            Table2:

            LOAD * INLINE [

                 CountryCode,CountryName,ProductName,CountryPotential,BillingYear,SalesForecast

            AU,Australia,Product1,30,2015,12

            AU,Australia,Product1,30,2016,15

            AU,Australia,Product2,20,2015,12

            AU,Australia,Product2,20,2016,15

            AT,Austria,Product1,24,2015,8

            AT,Austria,Product1,24,2016,10

            BA,Bosnia and Herzegovina,Product2,3,2015,1

            BA,Bosnia and Herzegovina,Product2,3,2016,

            2

            ];

            • Re: Joining table on multiple keys - how to
              jagan mohan rao appala

              Hi Jeff,

               

              You should not join both the tables it will duplicate the records, so you have to go with the Linktable like below

               

              Table1:

              LOAD

              AutoNumber(CountryCode & ProductName) AS Key,

              *

              INLINE [

              CountryCode,ProductName,ClientID,ClientName,Billing Date,Curr.,TotalBillValue,Bill. Qty in SKU,SalesMan

              AU,Product1,100,ABC,10/03/2015,EUR,1,000.00,1,John

              AU,Product2,100,ABC,10/03/2015,EUR,100.00,1,John

              AU,Product1,101,EFG,10/03/2015,EUR,1,000.00,1,John

              AU,Product2,101,EFG,10/03/2015,EUR,100.00,1,John

              AT,Product1,102,HIJ,10/03/2015,EUR,2,000.00,2,Henry

              AT,Product2,102,HIJ,10/03/2015,EUR,300.00,3,Henry

              BA,Product1,103,KLM,10/03/2015,EUR,5,000.00,5,Henry

              BA,Product2,103,KLM,10/03/2015,EUR,200.00,2,Henry

              ];

               

              LinkTable:

              LOAD

              DISTINCT Key, CountryCode,ProductName

              RESIDENT Table1;

               

              Table2:

              LOAD

              AutoNumber(CountryCode & ProductName) AS Key,

              *

              INLINE [

                   CountryCode,CountryName,ProductName,CountryPotential,BillingYear,SalesForecast

              AU,Australia,Product1,30,2015,12

              AU,Australia,Product1,30,2016,15

              AU,Australia,Product2,20,2015,12

              AU,Australia,Product2,20,2016,15

              AT,Austria,Product1,24,2015,8

              AT,Austria,Product1,24,2016,10

              BA,Bosnia and Herzegovina,Product2,3,2015,1

              BA,Bosnia and Herzegovina,Product2,3,2016,2

              ];

              Concatenate(LinkTable)

              LOAD

              DISTINCT Key, CountryCode,ProductName

              RESIDENT Table2;

               

               

              DROP FIELDS CountryCode,ProductName FROM Table1, Table2;

               

              Hope this helps you.

               

              Regards,

              Jagan.

              • Re: Joining table on multiple keys - how to
                arjun rao

                Hi,

                Please use this script. This is working for me.

                 

                Table1:

                LOAD *,

                AutoNumber(CountryCode & ProductName) AS Key;

                LOAD CountryCode,

                    ProductName,

                    ClientID,

                    ClientName,

                    [Billing Date],

                    Curr.,

                    TotalBillValue,

                    [Bill. Qty in SKU],

                    SalesMan

                FROM

                [Potential and forecast - QlikCommunity.xlsx]

                (ooxml, embedded labels, table is Table1);

                LinkTable:

                LOAD

                DISTINCT Key, CountryCode,ProductName

                RESIDENT Table1;

                Table2:

                LOAD *,

                AutoNumber(CountryCode & ProductName) AS Key;

                LOAD CountryCode,

                    CountryName,

                    ProductName,

                    CountryPotential,

                    BillingYear,

                    SalesForecast

                  

                FROM

                [Potential and forecast - QlikCommunity.xlsx]

                (ooxml, embedded labels, table is Table2);

                Concatenate(LinkTable)

                LOAD

                DISTINCT Key, CountryCode,ProductName

                RESIDENT Table2;

                • Re: Joining table on multiple keys - how to
                  Sasidhar Parupudi

                  Please try the attachment

                  hth

                  Sasi

                   

                  Untitled2.jpg

                  qualify CountryCode,ProductName;

                  Table1:

                   

                  LOAD

                    CountryCode&'-'& ProductName&'-'&year(date#([Billing Date],'DD/MM/YYYY')) as Key,

                        * INLINE [

                  CountryCode,ProductName,ClientID,ClientName,Billing Date,Curr.,TotalBillValue,Bill. Qty in SKU,SalesMan

                  AU,Product1,100,ABC,10/03/2015,EUR,"1000.00",1,John

                  AU,Product2,100,ABC,10/03/2015,EUR,"100.00",1,John

                  AU,Product1,101,EFG,10/03/2015,EUR,"1000.00",1,John

                  AU,Product2,101,EFG,10/03/2015,EUR,"100.00",1,John

                  AT,Product1,102,HIJ,10/03/2015,EUR,"2000.00",2,Henry

                  AT,Product2,102,HIJ,10/03/2015,EUR,"300.00",3,Henry

                  BA,Product1,103,KLM,10/03/2015,EUR,"5000.00",5,Henry

                  BA,Product2,103,KLM,10/03/2015,EUR,"200.00",2,Henry

                   

                  ];

                   

                   

                   

                  JOIN (Table1)

                   

                  Table2:

                   

                  LOAD *,CountryCode&'-'& ProductName&'-'&BillingYear as Key INLINE [

                   

                  CountryCode,CountryName,ProductName,CountryPotential,BillingYear,SalesForecast

                   

                  AU,Australia,Product1,30,2015,12

                   

                  AU,Australia,Product1,30,2016,15

                   

                  AU,Australia,Product2,20,2015,12

                   

                  AU,Australia,Product2,20,2016,15

                   

                  AT,Austria,Product1,24,2015,8

                   

                  AT,Austria,Product1,24,2016,10

                   

                  BA,Bosnia and Herzegovina,Product2,3,2015,1

                   

                  BA,Bosnia and Herzegovina,Product2,3,2016,2

                   

                  ];

                   

                  NoConcatenate

                  final:

                  LOAD * Resident Table1 where isnull(ClientID)=0;

                   

                  drop table Table1;

                  • Re: Joining table on multiple keys - how to
                    arjun rao

                    Did you able to load the data?