Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

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

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.

Community Browser