Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
I have a table, CustID, Date of Purchase, ProdID, Quantity
Now I want to add few more columns identifying the most recent purchase done by same customer for that ProdID and its quantity.
This will help me identify number of days since last purchase for a product.
So the final table should look like:
CustID | ProdID | DtOfPur | Quantity | PrevDtOfPur | PrevQuantity |
123 | P1 | 01-01-2013 | 10 | - | - |
123 | P2 | 03-04-2013 | 20 | - | - |
123 | P1 | 03-04-2013 | 5 | 01-01-2013 | 10 |
123 | P2 | 05-06-2013 | 5 | 03-04-2013 | 20 |
123 | P3 | 05-06-2013 | 10 | - | - |
How do I load table to achieve this.
Please help
Hi
You'll have to make sure the data is loaded in the correct order and then use the peek() function your load script to look at the previous record's value for [Date of Purchase] and use that for [PrevDtOfPur] .
Best Regards, Bill
I would prefer front-end solution for this using FirstSortedValue() or sometimes Max() with combination of IsNull() in cases like yours.