Peek function

[#1]

Hi

I am strugling with little bit with peek function. I have inventories in data table. In this table you can see inventory per period if there has been material movements within the period. If there is no value in this table for some period, it means that the inventory value is same as previous period, so in case there is no movements within in period I need to use peek function to return the last value for the period.

I have now following script

Load

AccumulationKey,
if(len(trim(MonthEndValue))=0,peek(MonthEndValue),MonthEndValue) as StockValue,
if(len(trim(MonthEndValue))=0,peek(MonthEndVolume),MonthEndVolume) as StockVolume,
Year,
Month,
Warehouse,
Product

Resident ValAccum;

This script doesn't work too properly. As you can see below, in case there is now inventory value in some cases it returns the last value from the last series In Example below it returns the last value from the "group" 4444|11111 to 4444|22222 cause there isn't any data for 4444|22222 before Feb-2013.

How should I change the formula to say QlikvIew to stop searching the last value when the group change (AccumulationKey)?

AccumulationKey	Stock value	Stock volume	Year	Month	Warehouse	Product
4444|11111	589.9	100	2013	Sep	4444	11111
4444|11111	589.9	100	2013	Oct	4444	11111
4444|22222	589.9	100	2012	Feb	4444	22222
4444|22222	589.9	100	2012	Mar	4444	22222
4444|22222	589.9	100	2012	Apr	4444	22222
4444|22222	589.9	100	2012	May	4444	22222
4444|22222	589.9	100	2012	Jun	4444	22222
4444|22222	589.9	100	2012	Jul	4444	22222
4444|22222	0.0	0	2012	Aug	4444	22222
4444|22222	0.0	0	2012	Sep	4444	22222
4444|22222	0.0	0	2012	Oct	4444	22222
4444|22222	0.0	0	2012	Nov	4444	22222
4444|22222	0.0	0	2012	Dec	4444	22222
4444|22222	0.0	0	2013	Jan	4444	22222
4444|22222	3837.6	600	2013	Feb	4444	22222

Many thanks already in advance!

Regards

Janne

[swuehl · Accepted solution]

if(peek(AccumulationKey) = AccumulationKey and len(trim( ...

[#3]

Thanks! this works!

Regards

Janne