Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

flygstolen_fred
New Contributor III

Lines fetched repeats

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)

1 Solution

Accepted Solutions
MVP
MVP

Re: Lines fetched repeats

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;

10 Replies
MVP
MVP

Re: Lines fetched repeats

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;

flygstolen_fred
New Contributor III

Re: Lines fetched repeats

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)

MVP
MVP

Re: Lines fetched repeats

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?

flygstolen_fred
New Contributor III

Re: Lines fetched repeats

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?

MVP
MVP

Re: Lines fetched repeats

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;

flygstolen_fred
New Contributor III

Re: Lines fetched repeats

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?


MVP
MVP

Re: Lines fetched repeats

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

flygstolen_fred
New Contributor III

Re: Lines fetched repeats

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

MVP
MVP

Re: Lines fetched repeats

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;

Community Browser