topic How to retrieve second last value within a product group? in QlikView

How to retrieve second last value within a product group?

Thu, 29 Mar 2012 08:43:13 GMT

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

How to retrieve second last value within a product group?

Thu, 29 Mar 2012 08:54:34 GMT

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;