Retail data identify the most recent purchase by CustID

[#1]

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

[Anonymous]

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

[tresesco]

I would prefer front-end solution for this using FirstSortedValue() or sometimes Max() with combination of IsNull() in cases like yours.