3 Replies Latest reply: Sep 25, 2017 2:47 PM by Anand Chouhan RSS

    How to order Load statements

    Kerim Koc

      Hello Community,

       

      I would like to understand the logic of ordering Load-Statements.

       

      The following script doesn't work as intended. I'm trying to make a "Rangesum" with the Peek()-function. It looks good at the first glance, but if I try to make a separate Load-statement to order the Sales, an error occurs.

       

      //*3.* Finally, I'm trying to safe the table temporarilly and order by Customer_Aggr

      OrderAttemp:

      LOAD *

      Resident Operations Order By Customer_Aggr;

       

      //*2.* Than I load the data and make the rangesum-operation with peek()

      Operations:

      Load

      Customer_Name,

      Customer_Place,

      Customer_Sales,

      Customer_Sales + Peek(Customer_Sales,-1) AS Customer_Aggr;

       

      //*1.*  Here I'm creating my data

      ExampleData:

      Load * Inline [

      Customer_ID, Customer_Name, Customer_Place, Customer_Sales

      1,Eric,Boston,450

      2,John,Kreta,500

      3,Rick,Texas,320

      4,Martin,Ohio,250

      5,Marcus,Kensas,140

      6,Lisa,Boston,659

      7,Geo,Kreta,370

      8,Monti,Kreta,220

      9,Daniel,Texas,180

      10,Carl,Kensas,220

      11,Adi,Kensas,840

      12,Begoli,Ohio,1

      13,Bruce,Texas,456

      14,Hillary,Kreta,6986];

       

      I did change the order several times, but didn't help. Could anyone explain me the reason why the script doesn't work and the logic of ordering load statements by the way?

       

      Best regards

       

      K.K

        • Re: How to order Load statements
          Anand Chouhan

          Try this

           

          //*2.* Than I load the data and make the rangesum-operation with peek()

          Operations:

          Load

          Customer_Name,

          Customer_Place,

          Customer_Sales,

          Rangesum( Customer_Sales + Peek(Customer_Sales,-1)) AS Customer_Aggr;

           

          //*1.*  Here I'm creating my data

          //ExampleData:

          Load * Inline [

          Customer_ID, Customer_Name, Customer_Place, Customer_Sales

          1,Eric,Boston,450

          2,John,Kreta,500

          3,Rick,Texas,320

          4,Martin,Ohio,250

          5,Marcus,Kensas,140

          6,Lisa,Boston,659

          7,Geo,Kreta,370

          8,Monti,Kreta,220

          9,Daniel,Texas,180

          10,Carl,Kensas,220

          11,Adi,Kensas,840

          12,Begoli,Ohio,1

          13,Bruce,Texas,456

          14,Hillary,Kreta,6986];

           

          ////*3.* Finally, I'm trying to safe the table temporarilly and order by Customer_Aggr

          NoConcatenate

          OrderAttemp:

          LOAD * Resident Operations Order By Customer_Aggr;

           

           

          DROP Table Operations;

            • Re: How to order Load statements
              Kerim Koc

              Hi,

               

              your changes don't work:

               

              Operations:

              Load

              Customer_Name,

              Customer_Date,

              Customer_Place,

              Customer_Sales,

              Rangesum( Customer_Sales + Peek(Customer_Sales,-1)) AS Customer_Aggr;

               

              ExampleData:

              Load * Inline [

              Customer_ID, Customer_Name,Customer_Date, Customer_Place, Customer_Sales

              1,Eric,15.04.2015,Boston,450

              2,John,18.04.2015,Kreta,500

              3,Rick,20.04.2015,Texas,320

              4,Martin,22.04.2015,Ohio,250

              5,Marcus,25.04.2015,Kensas,140

              6,Lisa,28.04.2015,Boston,659

              7,Geo,30.04.2015,Kreta,370

              8,Monti,07.05.2015,Kreta,220

              9,Daniel,10.05.2015,Texas,180

              10,Carl,15.05.2015,Kensas,220

              11,Adi,18.05.2015,Kensas,840

              12,Begoli,20.05.2015,Ohio,1

              13,Bruce,24.05.2015,Texas,456

              14,Hillary,28.05.2015,Kreta,6986];

               

              NoConcatenate

              OrderAttemp:

              LOAD *Resident Operations Order By Customer_Aggr;


              DROP Table Operations;

               

              Error Alert: Can't find Table Operations.