Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

flygstolen_fred
New Contributor III

Concatenate and drop table

Hi,

I'm concatenating 2 tables when certain conditions are matching but in the "dataloading window" I get +1000 rows with the message "Product << Markup". I probably need to change my load script or drop the Markup table somewhere.

Should I use the RESIDENT Markup in the for-loop?

Anyone can point me in the right direction? Thanks!


Product:

LOAD ProductCode,

    ProductDescription,

    ProductPrice;


Markup:

LOAD MarkupPrice,

    MarkupKey,

    MarkupCountryCode,

    MarkupDesc;

LET NumOfOrderRows = NoOfRows('Order');

LET NumOfMarkupRows = NoOfRows('Markup');

FOR i=0 to $(NumOfOrderRows)-1

  FOR j=0 to $(NumOfMarkupRows)-1

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

     

          CONCATENATE (Product)

          LOAD

               PEEK('MarkupKey', $(j), 'Markup') as ProductCode,

               PEEK('MarkupDesc', $(j), 'Markup') as ProductDescription,

               PEEK('MarkupPrice', $(j), 'Markup') as ProductPrice,

               RESIDENT Markup;

      END IF

  NEXT;

NEXT;

8 Replies
MVP
MVP

Re: Concatenate and drop table

Where is the script for the Booking table?

MVP
MVP

Re: Concatenate and drop table

I am not sure if the loop is equivalent to the below script, but give it a shot:

Product:

LOAD ProductCode,

    ProductDescription,

    ProductPrice;


Markup:

LOAD MarkupPrice,

    MarkupKey,

    MarkupCountryCode,

    MarkupDesc;

Concatenate(Product)

LOAD MarkupPrice as ProductPrice,

    MarkupKey as ProductCode,

    MarkupDesc as ProductDescription

Resident Markup

Where Exists(CountryCode, MarkupCountryCode);

MVP
MVP

Re: Concatenate and drop table

And where is the Order table defined?

Maybe you don't need the nested loops at all, maybe it's enough to use a WHERE clause using EXISTS:

Something along these lines:

Product:

LOAD ProductCode,

    ProductDescription,

    ProductPrice;



Markup:

LOAD MarkupPrice,

    MarkupKey,

    MarkupCountryCode,

    MarkupDesc;

CONCATENATE (Product)

LOAD MarkupKey as ProductCode,

          MarkupDesc, as ProductDescription,

          MarkupPrice as ProductProce

RESIDENT Markup

WHERE EXISTS(CountryCode, MarkupCountryCode);

Edit:

Sunny was faster as always, so only addition to his post would be my first line...

flygstolen_fred
New Contributor III

Re: Concatenate and drop table

Thanks for trying to help.

The load statement are approx 100 rows but I just cut out a part of it.

Every order has multiple products and I think I need the IF statement because I'm checking some more conditions and doing some calculations between product and markup prices etc.

Order:

LOAD OrderId,

CountryCode

Segment;

Product:

LOAD OrderId,

    ProductCode,

    ProductDescription,

    ProductPrice;


Markup:

LOAD MarkupPrice,

    MarkupKey,

    MarkupCountryCode,

    MarkupDesc;

MVP
MVP

Re: Concatenate and drop table

What are those other checks? May be all those can be done using where statement?

MVP
MVP

Re: Concatenate and drop table

In general, I would try to avoid the nested FOR loops for performance reasons.

Could you maybe post a simplified script and some sample lines of input data that show what you are finally trying to achieve?

flygstolen_fred
New Contributor III

Re: Concatenate and drop table

Thanks for trying to help out.

My functionality seems to work but I looks like the data load is doing a lot of extra work and something wrong.

The Markup table contains approx 100 rows but the Order table contains 100 000+ rows, that's why I put the iteration of Markup table inside the Order table iteration.

The expected output is that I wanna add extra InvoiceLines to each Order when the criteria in the Markup table are met.

The "extra products" in the Markup table also needs to be valid based on date.

Dependent of what Markup category I need to check different fields in Order table if it's valid, and the Price calculation could also be different.

Order:

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

Invoice:

InvoiceIdOrderId
7891234
7901234
7911235

InvoiceLines:

InvoiceIdCodeDescrPrice
789CPCDescription 1150
789CPODescription 2100
790CPKDescription 380
791CKJDescription 4105

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')) THEN

        IF (PEEK('Category', $(j), 'Markup') = 'GENERAL') THEN

              CONCATENATE (InvoiceLines)

              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,

                  RESIDENT Markup;

        END IF

        IF (PEEK('Category', $(j), 'Markup') = 'REFERER' AND PEEK('Code', $(j), 'Markup') = PEEK('Referer',$(i), 'Order')) THEN

              CONCATENATE (InvoiceLines)

              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') * PEEK('Total', $(j), 'Order')) as Price,

                  RESIDENT Markup;

        END IF

   

    END IF

  NEXT;

NEXT;


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

flygstolen_fred
New Contributor III

Re: Concatenate and drop table

Hi Swuehl,

Is it a performance problem if the outer loop contains 100 000+ rows and the inner loop just contains 100 rows?

I posted a more detailed load statement above to show what Im trying to do.