How to retrieve second last value within a product group?

[#1]

Hi,

I have the following table:

load

* inline
[Product, YearWeek, Value
A, 201207, 100
A, 201209, 200
A, 201214, 300
B, 201205, 150
B, 201219, 240
C, 201211, 360
D, 201204, 120
D, 201206, 400
D, 201207, 1200
D, 201218, 270
D, 201220, 890
];

I am trying to get the second last posted value within each product group. The result should be:

Product  YearWeek  Value

A          201209      200

B          201205      150

C          201211      360

D          201218      270

Any help would be appreciated.

Thanks

Raj

[#2 · Accepted solution]

Hi,

I found it myself.

Thanks anyway.

Raj

t1:

load * inline
[Product, YearWeek, Value
A, 201207, 100
A, 201209, 200
A, 201214, 300
B, 201205, 150
B, 201219, 240
C, 201211, 360
D, 201204, 120
D, 201206, 400
D, 201207, 1200
D, 201218, 270
D, 201220, 890
];

t2:
load *, Product&PrevWeek as PrevWeekKey;
load Product, if(isnull(max(YearWeek,2)),max(YearWeek),max(YearWeek,2)) as PrevWeek
resident t1
group by Product;

left join (t2)
load Product&YearWeek as PrevWeekKey,
Value
resident t1;

drop table t1;