10 Replies Latest reply: Jul 3, 2016 9:58 AM by fredrik olsson RSS

    Lines fetched repeats

    fredrik olsson

      Hi,

      I'm trying to concatenate to tables when certain conditions match (Category, Code, Countrycode and Dates in the Markup table).

      Everything seems to work as it should in the app but the output in the load data-window says: InvoiceLines << Markup, Lines fetched, thousand times. The value also increase for every row in the output window. Am I missing something crucial?

      Any help are appreciated.




      The Order table contains approx 300 000 rows and the markup table contains about 150 rows. Is the nested for-loop the best solutions if I need to check multiple conditons (category, dates, code, country) before concatenation?


      Order:

      OrderIdCountryCodeRefererTotalOrderDate
      1234SEGOOGLE3302016-01-05
      1235NONEWSLETTER1052016-01-15

       

      Invoice:

      InvoiceIdOrderId
      7891234
      7901234
      7911235

       

      InvoiceLines:

      InvoiceIdCodeDescrPriceCurrency
      789CPCDescription 1150SEK
      789CPODescription 2100SEK
      790CPKDescription 380SEK
      791CKJDescription 4105SEK

       

      Markup:

      CategoryCodeDescrCountryCodePriceFromDateToDate
      GENERALALLText1 - internalSE172016-01-012016-01-01
      GENERALALLText2 - internalNO152015-01-052015-12-31
      REFERERGOOGLEFeeSE0.12015-05-012016-12-31

       

      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') AND PEEK('Category', $(j), 'Markup') = 'REFERER' AND PEEK('Code', $(j), 'Markup') =PEEK('Referer',$(i), 'Order')) THEN

                    LOAD

                        Applymap('OrderInvoiceKeyMap', PEEK('OrderId',$(i), 'Order')) as InvoiceId,

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

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

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

                        IF (PEEK('CountryCode', $(i), 'Order') = 'SE', 'SEK',

                        IF (PEEK('CountryCode', $(i), 'Order') = 'DE', 'EUR', 'Unknown')) as Currency,

                        RESIDENT Markup;

       

              END IF

        NEXT j;

      NEXT i;


      (I use Applymap to get hold off the first InvoiceId for every Order)

        • Re: Lines fetched repeats
          Stefan Wühl

          Not sure if I understand what you are trying to do, but this

           

                 LOAD

                            Applymap('OrderInvoiceKeyMap', PEEK('OrderId',$(i), 'Order')) as InvoiceId,

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

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

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

                            IF (PEEK('CountryCode', $(i), 'Order') = 'SE', 'SEK',

                            IF (PEEK('CountryCode', $(i), 'Order') = 'DE', 'EUR', 'Unknown')) as Currency,

                            RESIDENT Markup;

           

          will basically append 150 identical rows (number of Markup records), while I assume you only want to append a single line:

           

                 LOAD

                            Applymap('OrderInvoiceKeyMap', PEEK('OrderId',$(i), 'Order')) as InvoiceId,

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

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

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

                            IF (PEEK('CountryCode', $(i), 'Order') = 'SE', 'SEK',

                            IF (PEEK('CountryCode', $(i), 'Order') = 'DE', 'EUR', 'Unknown')) as Currency

                            Autogenerate 1;

            • Re: Lines fetched repeats
              fredrik olsson

              Thanks swuehl for trying to help out.

               

              Im trying to add data from the Markup-table as extra invoicelines to every Order if the criteria in the If-statement are met. Every order will have approx 3-5 extra invoicelines added.

               

              Your solution also seems to work but the data load window stills output the following thousand times:

              InvoiceLines << AUTOGENERATE(1)

              Lines fetched: 6 147 (increase +1 for every message)

                • Re: Lines fetched repeats
                  Stefan Wühl

                  But the total number of appended records should be much smaller then?

                   

                  I think it's default behaviour that you get a script output line per table load (and that's what you are doing within the two nested FOR loops).

                   

                  I am not quite sure what your exact concern is, could you detail that a bit more?

                    • Re: Lines fetched repeats
                      fredrik olsson

                      Thanks again swehel.

                       

                      Yes, I seems like the number of appended rows are much smaller.

                      My question is if this is a good way of solving this problem? Could I append this markup rows to the InvoiceLines table in another way?

                        • Re: Lines fetched repeats
                          Stefan Wühl

                          Should be possible, mayb using something along these lines:

                           

                           

                           

                          TMP:
                          NOCONCATENATE
                          LOAD * RESIDENT Order;
                          
                          
                          INNER JOIN
                          LOAD 
                          //Category, 
                            Code as Referer,
                            Code,
                               Descr, 
                               CountryCode, 
                               Price, 
                          //     FromDate, 
                          //     ToDate,
                              IF (CountryCode  = 'SE', 'SEK',
                                           IF (CountryCode = 'DE', 'EUR', 'Unknown')) as Currency
                          Resident Markup
                          WHERE Category = 'REFERER';
                          
                          
                          Concatenate (InvoiceLine)
                          LOAD 
                             Applymap('OrderInvoiceKeyMap', OrderId) as InvoiceId,
                                       Code,
                                       Descr,
                                       Price,
                                    Currency
                          RESIDENT TMP;
                          
                          
                          DROP TABLE TMP;
                          
                          
                          //
                          DROP table Markup;
                          
                            • Re: Lines fetched repeats
                              fredrik olsson
                              Thanks again for trying to help. 

                              This maybe could be a better solution, but how do I make sure that the Order date are between from/to date in the markup table before joining?


                                • Re: Lines fetched repeats
                                  Stefan Wühl

                                  Where in your existing posted code are you taking care of that?

                                    • Re: Lines fetched repeats
                                      fredrik olsson

                                      My thought was to add it to the IF-statement like this:

                                       

                                      IF (PEEK('CountryCode', $(j), 'Markup') = PEEK('CountryCode', $(i), 'Order') AND PEEK('Category', $(j), 'Markup') = 'REFERER' AND PEEK('Code', $(j), 'Markup') =PEEK('Referer',$(i), 'Order') AND DATEDIFF(day, PEEK('FromDate', $(j), 'Markup'), PEEK('OrderDate'', $(i), 'Order'))>=0 AND DATEDIFF(day, PEEK('ToDate', $(j), 'Markup'), PEEK('OrderDate'', $(i), 'Order'))<0) THEN

                                        • Re: Lines fetched repeats
                                          Stefan Wühl

                                          You should be able to do this with a simple WHERE clause:

                                           

                                           

                                           

                                          Order:

                                          LOAD OrderId,

                                              CountryCode,

                                              Referer,

                                              Total,

                                              OrderDate

                                          FROM

                                          [https://community.qlik.com/thread/219708]

                                          (html, codepage is 1252, embedded labels, table is @1);

                                           

                                           

                                          Invoice:

                                          LOAD InvoiceId,

                                              OrderId

                                          FROM

                                          [https://community.qlik.com/thread/219708]

                                          (html, codepage is 1252, embedded labels, table is @2);

                                           

                                           

                                          InvoiceLine:

                                          LOAD InvoiceId,

                                              Code,

                                              Descr,

                                              Price,

                                              Currency

                                          FROM

                                          [https://community.qlik.com/thread/219708]

                                          (html, codepage is 1252, embedded labels, table is @3);

                                           

                                           

                                          Markup:

                                          LOAD Category,

                                              Code,

                                              Descr,

                                              CountryCode,

                                              Price,

                                              FromDate,

                                              ToDate

                                          FROM

                                          [https://community.qlik.com/thread/219708]

                                          (html, codepage is 1252, embedded labels, table is @4);

                                           

                                           

                                          Concatenate

                                          LOAD * INLINE [

                                          Category, Code, Descr, CountryCode, Price, FromDate, ToDate

                                          REFERER, NEWSLETTER, Fee 2, NO, 10, 2016-01-01, 2016-01-16

                                          REFERER, NEWSLETTER, Fee 3, NO, 11, 2016-01-01, 2016-01-15

                                          REFERER, NEWSLETTER, Fee 4, NO, 12, 2016-01-15, 2016-01-30

                                          REFERER, NEWSLETTER, Fee 5, NO, 13, 2016-01-16, 2016-01-30

                                          ];

                                           

                                          TMP:

                                          NOCONCATENATE

                                          LOAD * RESIDENT Order;

                                           

                                           

                                          INNER JOIN

                                          LOAD

                                          //Category,

                                            Code as Referer,

                                            Code,

                                              Descr,

                                              CountryCode,

                                              Price,

                                              FromDate,

                                              ToDate,

                                              IF (CountryCode  = 'SE', 'SEK',

                                                          IF (CountryCode = 'DE', 'EUR', 'Unknown')) as Currency

                                          Resident Markup

                                          WHERE Category = 'REFERER';

                                           

                                           

                                          Concatenate (InvoiceLine)

                                          LOAD

                                            Applymap('OrderInvoiceKeyMap', OrderId) as InvoiceId,

                                                      Code,

                                                      Descr,

                                                      Price,

                                                    Currency

                                          RESIDENT TMP

                                          WHERE OrderDate >= FromDate and OrderDate < ToDate;

                                           

                                           

                                          DROP TABLE TMP;

                                           

                                          DROP table Markup;