Concatenate and drop table

[flygstolen_fred]

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;

[sunny_talwar]

Where is the script for the Booking table?

[sunny_talwar]

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);

[swuehl]

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]

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;

[sunny_talwar]

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

[swuehl]

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]

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:

OrderId	CountryCode	Referer	Total	OrderDate
1234	SE	GOOGLE	330	2016-01-05
1235	NO	NEWSLETTER	105	2016-01-15

Invoice:

InvoiceId	OrderId
789	1234
790	1234
791	1235

InvoiceLines:

InvoiceId	Code	Descr	Price
789	CPC	Description 1	150
789	CPO	Description 2	100
790	CPK	Description 3	80
791	CKJ	Description 4	105

Markup:

Category	Code	Descr	CountryCode	Price	FromDate	ToDate
GENERAL	ALL	Text1 - internal	SE	17	2016-01-01	2016-01-01
GENERAL	ALL	Text2 - internal	NO	15	2015-01-05	2015-12-31
REFERER	GOOGLE	Fee	SE	0.1	2015-05-01	2016-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]

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.