topic Retrieving previous row value for certain ID in QlikView

Retrieving previous row value for certain ID

Tue, 24 Feb 2015 09:28:17 GMT

Hi,

I've been looking into the Peek() function in order to achieve what I am after, but i've not been able to get the results that i'd like. Can someone look at the scenario below and see if i've just missed something off, or whether I should be using something different?

I have a table with order IDs and years/months that they have been invoiced in (as the orders can be invoiced over several months). What I want to get in my model per row is the order ID, the year/month of this invoice, and the year/month of the previous invoice. Example data is below:

Order ID YearMonth (stored as a single int value)

12345 201401

12345 201402

12345 201403

12345 201405

I've tried to use the Peek() function in my script like Peek(YearMonth, -1), but instead of getting what i expect (so for the last line I would expect the peek to return 201403) instead I get something like below:

Order ID YearMonth PrevYearMonth

12345 201401 201401

12345 201402 201401

12345 201403 201402

12345 201403 201403

12345 201405 201403

12345 201405 201405

So i'm not sure what is going wrong. There are other orders and invoices in the table as well, but i have set the table to order by the order ID and the year/month, so I would have thought that it wouldn't be picking up ones from other orders (except probably the first line).

Can anyone help?

Re: Retrieving previous row value for certain ID

Tue, 24 Feb 2015 09:43:44 GMT

Hi James,

looking at your output table, the peek looks to be doing what is expected of it, simply giving you the previous YearMonth (exactly as it should), I think what you are after is some additional logic rather than just a pure peek.

Something like the below I believe should work.
(the rangemin is in there so you don't get a null on the first row)

Load*,
If(YearMonth=Peek('YearMonth'),Peek('PriorYearMonth',-1),RangeMin(Peek('YearMonth'),YearMonth)) As PriorYearMonth
;
load * inline [
Order ID , YearMonth
12345, 201401
12345, 201402
12345, 201403
12345, 201403
12345, 201405
12345, 201405
]
;

Hope that helps

Joe

Re: Retrieving previous row value for certain ID

Tue, 24 Feb 2015 10:11:08 GMT

That looks great, exactly what I need.

Thank you very much Joe.

Re: Retrieving previous row value for certain ID

hariprasadqv — Tue, 24 Feb 2015 10:13:18 GMT

use previous()

Re: Retrieving previous row value for certain ID

Tue, 24 Feb 2015 10:16:15 GMT

No worries glad to help