Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Previous or Peek function???

Hi

I have a problem with my data. I know this should be fairly easy to solve with previous or peek functions, but I am not very familiar with them and can't make this work.

I have following problem in my data

periodquantityvaluation
1111

1,1

22221,2
3333-
4444-
5555-
66661,3
7777-
88881,5

I have a inventory data in where I have moving average valuation (in this example above "valuation"). However, I am missing this moving average value in every case in where I have no movements in the period. If there is no value, I would need to use the previous existing one. So in this case valuation in period 3 should be 1,2 and same thing in period 4 and 5. In period 7 I would need to use the value from period 6.

Thanks already beforehand

Regards

Janne

1 Solution

Accepted Solutions
Gysbert_Wassenaar

See attached example


talk is cheap, supply exceeds demand

View solution in original post

6 Replies
Gysbert_Wassenaar

In this case you need to use peek since there are several records one after the other that have no valuation.

load

    period

    ,quantity

    ,if(len(trim(valuation))=0,peek(valuation),valuation) as valuation

from ...somewhere...;


talk is cheap, supply exceeds demand
Not applicable
Author

Hi,

Thanks, but I think this didn't worked yet. Please see my attached example

Gysbert_Wassenaar

See attached example


talk is cheap, supply exceeds demand
Not applicable
Author

Great, Thanks

-Janne

Not applicable
Author

Hi

And thanks again for the last tip.

However, I found out that script doesn't work always. It is saying now that if value is missing or zerrow it peak the last one. However it is possible that sometimes value is 0 and then script should return that one. So how to modify the script so that it peaks the last one only in case that it is totallu missing?

Accumulation keyPeriodValue in original dataWanted value
1000-11111Jan-1322
1000-11111Feb-13-2
1000-11111Mar-131,51,5
1000-11111Apr-1300
1000-11111May-13-0

In the example above I have accumulation key which contains location and product id. I have used that to group the data.

Thanks

Regards

Janne