Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
if(peek(AccumulationKey) = AccumulationKey and len(trim( ...
if(peek(AccumulationKey) = AccumulationKey and len(trim( ...
Thanks! this works!
Regards
Janne