10 Replies Latest reply: May 26, 2016 5:44 AM by Chandrasheker Gompa RSS

    Add / concatenate data to table

    fredrik olsson

      Hi,

       

      I trying to merge / concatenate data from 2 different tables in a for-loop. Based on conditions I'm adding new InvoiceLines with data from Markup table. Im iterating the Order and Markup table but I think I need to get hold of the "InvoiceId" related to the current Order to be able to add the new InvoiceLines. Should I use Lookup?

      Another thought, should I drop the Markup table after the for-loop?

       

      Any help are much appreciated.

       

      Order:

      OrderIdCountryCode
      1234SE
      1235NO

       

      Invoice:

      InvoiceIdOrderId
      7891234
      7901234
      7911235

       

      InvoiceLines:

      InvoiceLineIdInvoiceIdCodeDescrPrice
      45645789CPCDescription 1150
      45871789CPODescription 2100
      46987790CPKDescription 380
      54679791CKJDescription 4105

       

      Markup:

      CodeDescrCountryCodePrice
      HKJExtra productSE17
      JKBExtra productNO15

       

      LET NumOfOrderRows = NoOfRows('Order');

      LET NumOfMarkupRows = NoOfRows('Markup');

       

      FOR i=0 to $(NumOfOrderRows)-1

        FOR j=0 to $(NumOfMarkupRows)-1

          IF (PEEK('CountryCode', $(j), 'Markup') = PEEK('CountryCode', $(i), 'Order')) THEN

                CONCATENATE (InvoiceLines)

                LOAD 0 as InvoiceLineId,

                     PEEK('Code', $(j), 'Markup') as Code,

                     PEEK('Descr', $(j), 'Markup') as Descr,

                     PEEK('Price', $(j), 'Markup') as Price,

                     //How do I lookup a InvoiceId related to current Order?

                     RESIDENT Markup; //Is this necessary?

          END IF

        NEXT;

      NEXT;

        • Re: Add / concatenate data to table
          Chandrasheker Gompa

          Hi Fredrick,

           

          Once you have loaded the data from the Mark up table you can drop it as you have already concatenated the data and to get the invoice id for the current order you can do a look up on the invoice table by doing a join on the Invoiceid as it seems to be the key field..

           

          Thanks

          Chandra

            • Re: Add / concatenate data to table
              fredrik olsson

              Thanks for trying to help out Chandra.

              Could you point me in the right direction where to put the join?

                • Re: Add / concatenate data to table
                  Chandrasheker Gompa

                  Hi Fredrik,

                   

                  Please find the code I have written to serve your purpose.. this concatenates your InvoiceLines table:

                   


                  Markup_Temp:
                  LOAD
                      Code,
                      Descr,
                      CountryCode,
                      Price
                  FROM [lib://Desktop/Copy of SampleYoYEnrollmentOnly.xls]
                  (biff, embedded labels, table is Markup$);

                  join(Markup_Temp)
                  LOAD
                      OrderId,
                      CountryCode
                  FROM [lib://Desktop/Copy of SampleYoYEnrollmentOnly.xls]
                  (biff, embedded labels, table is Order$);

                  join(Markup_Temp)
                  LOAD
                      InvoiceId,
                      OrderId
                  FROM [lib://Desktop/Copy of SampleYoYEnrollmentOnly.xls]
                  (biff, embedded labels, table is Invoice$);


                  [InvoiceLines$]:
                  Load 0 as [InvoiceLineId],
                             InvoiceId,
                             [Code],
                          [Descr],
                          [Price] Resident Markup_Temp;
                            
                             concatenate
                  LOAD [InvoiceLineId],
                  [InvoiceId],
                  [Code],
                  [Descr],
                  [Price]
                  FROM [lib://Desktop/Copy of SampleYoYEnrollmentOnly.xls]
                  (biff, embedded labels, table is InvoiceLines$);

                  Drop Table Markup_Temp;

                   

                  Note: Please mark correct or helpful so that other members can refer in future

                   

                  Thanks

                  CHandra

                    • Re: Add / concatenate data to table
                      fredrik olsson

                      Hi,

                       

                      Thanks for trying to help.

                      Do I need to change the order what tables to load first?

                      Se my LOAD script below, do I need to move the Markup LOAD to the top?

                      Why do I need to JOIN Markup_Temp after the LOAD script?

                       

                      Qlik Sense connects all data/fields correctly between tables except the markup table.

                        • Re: Add / concatenate data to table
                          Chandrasheker Gompa

                          Hi,

                           

                          What I have done is created a temp table to be dropped later called Markup_Temp  and loaded all the other 3 table s by joining on the order id to get the Invoice id into my temp table and concatenated to the Base Fact InvoiceLines Table and dropped the markup Temp table ... and I checked the result it appended to the Invoicelines table without any problem... I have created your temp data in my excel so that's what you see in the from for the tables...

                           

                          Thanks

                          Chandra

                            • Re: Add / concatenate data to table
                              fredrik olsson

                              Is it possible to change to order because I have all the load scripts in different tabs in the "data import view".

                              I need to be able to "comment out" the markup-part sometimes when I load new data.

                               

                              Is this the best order to load data?

                              The markup table contains approx 100 rows and the Order table contains more than 250 000 rows.

                               

                              Tab1:

                              Order:

                              LOAD

                                   OrderId,

                                   CountryCode;

                               

                              Invoice:

                              LOAD

                                   InvoiceId,

                                   OrderId;

                               

                              InvoiceLines:

                              LOAD

                                   InvoiceLineId,

                                   InvoiceId,

                                   Code,

                                   Descr,

                                   Price;

                               

                              Tab2:

                              Markup:

                              LOAD

                                  Code,

                                  Descr,

                                  Price,

                                  CountryCode;

                               

                              LET NumOfOrderRows = NoOfRows('Order');

                              LET NumOfMarkupRows = NoOfRows('Markup');

                               

                              FOR i=0 to $(NumOfOrderRows)-1

                                FOR j=0 to $(NumOfMarkupRows)-1

                                  IF (PEEK('CountryCode', $(j), 'Markup') = PEEK('CountryCode', $(i), 'Order')) THEN

                                        CONCATENATE (InvoiceLines)

                                        LOAD 0 as InvoiceLineId,

                                             PEEK('Code', $(j), 'Markup') as Code,

                                             PEEK('Descr', $(j), 'Markup') as Descr,

                                             PEEK('Price', $(j), 'Markup') as Price,

                                             //How do I lookup a InvoiceId related to current Order?

                                             RESIDENT Markup; //Is this necessary?

                                  END IF

                                NEXT;

                              NEXT;

                                • Re: Add / concatenate data to table
                                  Chandrasheker Gompa

                                  The order of loading the table doesn't matter I have given you the logic of how to achieve the concatenation to the InvoiceLines table. The order table , Invoice table are just look up tables and mark up table is the table which has the fresh data or new data so ultimately you want all the data from mark up table and the invoice id from the invoice table which will be achieved by looking up to that table with the Order id .. So wherever (tabs) you load the data that's fine but only ensure you get all the info in one temp table before concatenating and drop the temp table..  

                                    • Re: Add / concatenate data to table
                                      fredrik olsson

                                      Thanks for trying to help out.

                                      I can't get it to work by adding join(markup) before the order and invoice table.

                                      Is maybe the Lookup or ApplyMap a better solution?

                                      Is it possible to add Lookup inside the for-loop to get the InvoiceId?

                                       

                                      Is this possible at all?

                                      LOOKUP('InvoiceId', 'OrderId', PEEK('OrderId',$(i), 'Order'), 'Invoice');

                                        • Re: Add / concatenate data to table
                                          Chandrasheker Gompa

                                          Hi,

                                           

                                          There are 2 ways of doing this if you are loading the data into QVD's then you can use them in the below script or if you doing direct load from the source then the below script as it is will work. Please not that ultimately in you data model viewer there should be one table only with the appended data. Please confirm if that is not the requirement.

                                           


                                          MarkupTemp:
                                          LOAD
                                          Code as CODE,
                                              Descr AS DESCR,
                                              CountryCode AS COUNTRYCODE,
                                              Price AS PRICE
                                          Resident Markup_table;
                                             
                                          join(MarkupTemp)
                                          LOAD
                                          OrderId AS ORDERID,
                                              CountryCode AS COUNTRYCODE
                                          Resident Order_table;
                                             
                                          join(MarkupTemp)
                                          LOAD
                                          InvoiceId AS INVOICEID,
                                              OrderId AS ORDERID
                                          Resident Invoice_Table;
                                            

                                          [InvoiceLines]:


                                          Load 0  AS INVOICELINEID,
                                                      INVOICEID,
                                                      CODE,
                                                      DESCR,
                                                   PRICE
                                                     Resident MarkupTemp;
                                             concatenate       

                                          LOAD [InvoiceLineId] AS INVOICELINEID,
                                          [InvoiceId]AS INVOICEID,
                                          [Code] AS CODE,
                                          [Descr] AS DESCR,
                                          [Price] AS PRICE
                                          FROM [lib://Desktop/Copy of SampleYoYEnrollmentOnly.xls]
                                          (biff, embedded labels, table is InvoiceLines$);


                                                    
                                          Drop Table MarkupTemp;
                                            Drop Table Markup_table;
                                             Drop Table Order_table;
                                              Drop Table Invoice_Table;

                            • Re: Add / concatenate data to table
                              fredrik olsson

                              This is my LOAD statement, where do I add the join between Order and Invoice table to lookup the InvoiceId in the for-loop?

                               

                              Order:

                              LOAD

                                   OrderId,

                                   CountryCode;

                               

                              Invoice:

                              LOAD

                                   InvoiceId,

                                   OrderId;

                               

                              InvoiceLines:

                              LOAD

                                   InvoiceLineId,

                                   InvoiceId,

                                   Code,

                                   Descr,

                                   Price;

                               

                              Markup:

                              LOAD

                                  Code,

                                  Descr,

                                  Price,

                                  CountryCode;

                               

                              LET NumOfOrderRows = NoOfRows('Order');

                              LET NumOfMarkupRows = NoOfRows('Markup');

                               

                              FOR i=0 to $(NumOfOrderRows)-1

                                FOR j=0 to $(NumOfMarkupRows)-1

                                  IF (PEEK('CountryCode', $(j), 'Markup') = PEEK('CountryCode', $(i), 'Order')) THEN

                                        CONCATENATE (InvoiceLines)

                                        LOAD 0 as InvoiceLineId,

                                             PEEK('Code', $(j), 'Markup') as Code,

                                             PEEK('Descr', $(j), 'Markup') as Descr,

                                             PEEK('Price', $(j), 'Markup') as Price,

                                             //How do I lookup a InvoiceId related to current Order?

                                             RESIDENT Markup; //Is this necessary?

                                  END IF

                                NEXT;

                              NEXT;