3 Replies Latest reply: Dec 11, 2013 5:23 AM by Eduard Cabanas Gili RSS

    Any idea how to do that ?

    Eduard Cabanas Gili

      Hi guys,

       

       

      I would like to know the days between the sales and customer.

      I have the  following table and I wanna know the days column

       

      Many Thank's

       

      SalesidCustomerDateDays
      1a31/01/2013
      2b31/01/2013
      3c31/01/2013
      4a15/02/201315
      5b20/02/201320
      6c25/02/201325
      7a01/03/201314
      8b10/03/201318
      9c25/03/201328
        • Re: Any idea how to do that ?
          jagan mohan rao appala

          Hi,

           

          Try this script

           

          Temp:

          LOAD

          Salesid,

          Customer,

          Date(Date#(Date, 'DD/MM/YYYY'), 'DD/MM/YYYY') AS Date

          Inline [

          Salesid, Customer, Date

          1, a, 31/01/2013

          2, b, 31/01/2013

          3, c, 31/01/2013

          4, a, 15/02/2013

          5, b, 20/02/2013

          6, c, 25/02/2013

          7, a, 01/03/2013

          8, b, 10/03/2013

          9, c, 25/03/2013

          ];

           

           

          Data:

          NoConcatenate

          LOAd

          *,

          If(Previous(Customer) = Customer, Ceil(Num(Date) - Previous(Num(Date)))) AS Days

          RESIDENT Temp

          ORDER BY Customer, Date;

           

           

          DROP TABLE Temp;

           

          Regards,

          Jagan.

          • Re: Any idea how to do that ?
            Josetxo Amonarriz

            Hello,

             

            please try this:

             

            Table1:

            LOAD * INLINE [

                SalesID, Customer, Date

                1,a,31/01/2013

                2,b,31/01/2013

                3,c,31/01/2013

                4,a,15/02/2013

                5,b,20/02/2013

                6,c,25/02/2013

                7,a,01/03/2013

                8,b,10/03/2013

                9,c,25/03/2013

            ];

             

            noconcatenate

            Table2:

            load SalesID,

            if(previous(Customer) <> Customer, 0, Date-previous(Date)) as Days,

            Customer,

            Date

              resident Table1 order by Customer, Date ASC;

             

            drop table Table1;

             

            Hope this helps.