3 Replies Latest reply: Oct 8, 2012 6:15 AM by Miguel Angel Baeyens de Arce RSS

    How to input a field and joining the tables

    Hennie Welman

      Hi, I'm not too familiar using the JOIN statement or how to join tables so I hope one of you clever guys out there can help me.

       

      I've got two tables for sales for two different branches for which I want to load data. The field names in the Sales tables are exactly the same so they are automatically joined. There are no fields within the tables which indicates for which branch the data is. I therefore want to associate the tables with a specific branch by loading a field name "Branch", with the different branches as values within the field. I loaded a table using inline after each Sales table to input the branches and used the JOIN statement to join the inline table with the sales table (see below).

       

      
      //SalesMasterJHB
      Directory;
      SalesMaster:
      LOAD
                 DocumentNumber, 
           DocumentType, 
           Date, 
           Period, 
           CustomerCode, 
           CustomerName, 
           [Customer Code Name], 
           SalesmanCode, 
           SalesmanName, 
           OrderNumber, 
           CostCode, 
           DeliveryAddress01, 
           DeliveryAddress02, 
           DeliveryAddress03, 
           DeliveryAddress04, 
           DeliveryAddress05, 
           Description, 
           QuoteClosingDate, 
           ProductCode, 
           [Product Name], 
           [Product Code Name], 
           [Product Category], 
           Unit, 
           Quantity, 
           UnitCost, 
           TotalLineCost, 
           UnitSell, 
           InclusivePrice, 
           TotalBeforeDiscount, 
           TotalDiscountPercent, 
           TotalDiscount, 
           TotalLineSale, 
           TaxAmount, 
           [Line Total Incl], 
           GP, 
           Physical, 
           StoreCode
      FROM
      [...\Sales Master 1 August - 31 August 2012 final JHB.xlsx]
      (ooxml, embedded labels, table is Sheet1);
      
      
      JOIN LOAD * Inline [
                 Branch,
                 JHB];
                 
      
      
      
      
      //SalesMasterBloem
      Directory;
      LOAD DocumentNumber, 
           DocumentType, 
           Date, 
           Period, 
           CustomerCode, 
           CustomerName, 
           [Customer Code Name], 
           SalesmanCode, 
           SalesmanName, 
           OrderNumber, 
           CostCode, 
           DeliveryAddress01, 
           DeliveryAddress02, 
           DeliveryAddress03, 
           DeliveryAddress04, 
           DeliveryAddress05, 
           Description, 
           QuoteClosingDate, 
           ProductCode, 
           [Product Name], 
           [Product Code Name], 
           [Product Category], 
           Unit, 
           Quantity, 
           UnitCost, 
           TotalLineCost, 
           UnitSell, 
           InclusivePrice, 
           TotalBeforeDiscount, 
           TotalDiscountPercent, 
           TotalDiscount, 
           TotalLineSale, 
           TaxAmount, 
           [Line Total Incl], 
           GP, 
           Physical, 
           StoreCode
      FROM
      [...\Sales Master 1 AUG - 31 AUG 2012 - BLOEM (FINAL).xlsx]
      (ooxml, embedded labels, table is Sheet1);
      
      
      
      
      JOIN LOAD * Inline [
                 Branch,
                 Bloem];
      
      

       

      However, when I reload the data, the script execution Progress stops at the following stage and the program doesn't respond once I abort the reloading process:

       

      SalesMaster << Sheet1 1,607 lines fetched
      INLFED << INLCC14 1 lines fetched
      Sheet1 1,754 lines fetched
      INLFED << INLCCC1 1 lines fetched
      
      

       

      Where can I change the script to correct it or is there an easier way to join fields with other tables? Any help will be much appreciated.

        • Re: How to input a field and joining the tables
          Miguel Angel Baeyens de Arce

          Hi Hennie,

           

          JOINs in QlikView happen when at least one of the fields in both tables are named alike. It seems that the Brach field only exists in your INLINE tables but not in your source LOADs, so it's likely to be killing QlikView making it all possible combinations between any of the rows in each table and Branch...

           

          EDIT: Ok, it seems I understood wrong your issue. I'd do the following:

           

          SalesMaster:
          LOAD *,
               'JHB' AS Branch
          FROM JHBFile ...;
          CONCATENATE (Table1) LOAD *,
               'Bloem' AS Branch
          FROM BloemFile ...;
          

           

          Hope that makes sense.

           

          Miguel

            • Re: How to input a field and joining the tables
              Hennie Welman

              Hi Miguel. Thanks for your reply. I changed the script and it works. Didn't think that to input new fields was as easy as "'JHB' as Branch". Just a question, is it necessary to use the "CONCATENATE" statement? And whats the function for inputting the Table name in brackets? It is to tell Qlikview which table (if you have more than one table in the script) to Concatenate with the table being loaded?

                • Re: How to input a field and joining the tables
                  Miguel Angel Baeyens de Arce

                  Hi Hennie,

                   

                  You're welcome! First: QlikView concantenates automatically when in the model are two tables or more that have the same exact number and name of fields, case sensitive. Second, it's best practice, although not mandatory, to make the code clearer, to specify that you are going to CONCATENATE (append) the lines in the coming table to the previously loaded table.

                   

                  As the script is executed from top to down sequentially, you may load a table in a different tab, and it makes the code easier to read when you specify the table you want to concatenate to.

                   

                  Hope that helps.

                   

                  Miguel