5 Replies Latest reply: Apr 1, 2016 5:58 PM by Sunny Talwar RSS

    Average order amount of all first orders

    Markus König

      Hi,

      I have an table of all orders like that:

       

      Customer
      OrdernumberAmount
      11

      10,00

      1215
      2312,50
      1413
      3520
      2623
      3711
      1810

       

      and i need only the amount of the first orders.

       

      Thanks for any helps.

        • Re: Average order amount of all first orders
          sreemannarayana g

          Hi Markus,

          Can you please try using the below script.

           

          input:

          load * inline [

          Customer,    Ordernumber,    Amount

          1,    1    , "10,00"

          1,    2,    "15"

          2,    3,    "12,50"

          1,    4,    "13"

          3,    5,    "20"

          2,    6,    "23"

          3,    7,    "11"

          1,    8,    "10"

          ];

           

          NoConcatenate

          output:

          load Customer,

               Ordernumber,

               Amount,

               if(Customer<>Previous(Customer),1,Peek(rank)+1) as rank

          Resident input Order by Customer, Ordernumber;

           

          NoConcatenate

          finaloutput:

          load Customer as customer,

               Ordernumber as ordernumber,

               Amount as amount

          Resident output where rank=1;    

           

           

          Thanks,
          Sreeman

          • Re: Average order amount of all first orders
            Marcus Sommer

            You could try it with firstsortedvalue() on the order number or an order date.

             

            - Marcus

            • Re: Average order amount of all first orders
              Hans Müller

              Maybe this... ?

              • Re: Average order amount of all first orders
                Sunny Talwar

                Couple of options to remove them from script:

                 

                1) Using Right Join

                 

                Table:

                LOAD * Inline [

                Customer,    Ordernumber,    Amount

                1,    1, 10.00

                1,    2,    15

                2,    3,    12.50

                1,    4,    13

                3,    5,    20

                2,    6,    23

                3,    7,    11

                1,    8,    10

                ];

                 

                Right Join (Table)

                LOAD Customer,

                  Min(Ordernumber) as Ordernumber

                Resident Table

                Group By Customer;

                 

                2) Using FirstSortedValue (as Mentioned by Marcus Above)

                 

                Table:

                LOAD Customer,

                  Min(Ordernumber) as Ordernumber,

                  FirstSortedValue(Amount, Ordernumber) as Amount

                Group By Customer;

                LOAD * Inline [

                Customer,    Ordernumber,    Amount

                1,    1, 10.00

                1,    2,    15

                2,    3,    12.50

                1,    4,    13

                3,    5,    20

                2,    6,    23

                3,    7,    11

                1,    8,    10

                ];

                 

                If you want to keep all the data but just want to show the Min ordernumber lines, then you can create flags for it

                 

                3) Flag

                 

                Table:

                LOAD * Inline [

                Customer,    Ordernumber,    Amount

                1,    1, 10.00

                1,    2,    15

                2,    3,    12.50

                1,    4,    13

                3,    5,    20

                2,    6,    23

                3,    7,    11

                1,    8,    10

                ];

                 

                Left Join (Table)

                LOAD Customer,

                  Min(Ordernumber) as Ordernumber,

                  1 as Flag

                Resident Table

                Group By Customer;