2 Replies Latest reply: Sep 16, 2015 8:18 AM by Yourinde Bauer RSS

    Inter record counting

      Hi everyone,

       

      A simplified example of the data which I'm loading into my model looks something like this:

       

      CustomerOrderIDOrderDate
      John

      10001

      2015-01-01
      Paul100022015-01-01
      Jane100032015-01-01
      John100042015-01-02
      Paul100052015-01-03
      Paul100062015-01-03
      Jane100072015-01-03
      Sue100082015-01-03

       

      I need to calculate a column in the script to keep track of the number of orders the customer has ever placed. i.e.:

       

      CustomerOrderIDOrderDatePurchaseNumber
      John

      10001

      2015-01-011
      Paul100022015-01-011
      Jane100032015-01-011
      John100042015-01-022
      Paul100052015-01-032
      Paul100062015-01-033
      Jane100072015-01-032
      Sue100082015-01-031
      John100092015-01-033

       

      This will allow me to tell that OrderID=10009 was John's third order ever. How do I calculate the PurchaseNumber column?