Retrieving previous row value for certain ID

[#1]

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?

[#2 · Accepted solution]

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

[#3]

That looks great, exactly what I need.

Thank you very much Joe.

[hariprasadqv]

use previous()

[#5]

No worries glad to help