Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
LOAD
Customer,
OrderId,
OrderDate,
AutoNumber(OrderId, Customer) as PurchaseNumber
FROM ...
LOAD
Customer,
OrderId,
OrderDate,
AutoNumber(OrderId, Customer) as PurchaseNumber
FROM ...
Brilliant, thank you!!