Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Not applicable

Retail data identify the most recent purchase by CustID

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:

CustIDProdIDDtOfPurQuantityPrevDtOfPurPrevQuantity
123P101-01-201310--
123P203-04-201320--
123P103-04-2013501-01-201310
123P205-06-2013503-04-201320
123P305-06-201310--

How do I load table to achieve this.

Please help

2 Replies
Highlighted

Re: Retail data identify the most recent purchase by CustID

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

Highlighted
MVP
MVP

Re: Retail data identify the most recent purchase by CustID

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