Hi everyone,
A simplified example of the data which I'm loading into my model looks something like this:
| Customer | OrderID | OrderDate |
|---|
| John | 10001 | 2015-01-01 |
| Paul | 10002 | 2015-01-01 |
| Jane | 10003 | 2015-01-01 |
| John | 10004 | 2015-01-02 |
| Paul | 10005 | 2015-01-03 |
| Paul | 10006 | 2015-01-03 |
| Jane | 10007 | 2015-01-03 |
| Sue | 10008 | 2015-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.:
| Customer | OrderID | OrderDate | PurchaseNumber |
|---|
| John | 10001 | 2015-01-01 | 1 |
| Paul | 10002 | 2015-01-01 | 1 |
| Jane | 10003 | 2015-01-01 | 1 |
| John | 10004 | 2015-01-02 | 2 |
| Paul | 10005 | 2015-01-03 | 2 |
| Paul | 10006 | 2015-01-03 | 3 |
| Jane | 10007 | 2015-01-03 | 2 |
| Sue | 10008 | 2015-01-03 | 1 |
| John | 10009 | 2015-01-03 | 3 |
This will allow me to tell that OrderID=10009 was John's third order ever. How do I calculate the PurchaseNumber column?
