8 Replies Latest reply: May 27, 2016 3:34 AM by fredrik olsson RSS

    Concatenate and drop table

    fredrik olsson

      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;

       

       

        • Re: Concatenate and drop table
          Sunny Talwar

          Where is the script for the Booking table?

            • Re: Concatenate and drop 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);

              • Re: Concatenate and drop table
                Stefan Wühl

                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...

                • Re: Concatenate and drop table
                  fredrik olsson

                  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;

                    • Re: Concatenate and drop table
                      Sunny Talwar

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

                        • Re: Concatenate and drop table
                          fredrik olsson

                          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)

                        • Re: Concatenate and drop table
                          Stefan Wühl

                          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?