topic Re: Previous or Peek function??? in QlikView

Previous or Peek function???

Mon, 08 Jul 2013 07:19:14 GMT

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

period	quantity	valuation
1	111	1,1
2	222	1,2
3	333	-
4	444	-
5	555	-
6	666	1,3
7	777	-
8	888	1,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

Re: Previous or Peek function???

Gysbert_Wassenaar — Mon, 08 Jul 2013 07:26:34 GMT

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...;

Re: Previous or Peek function???

luciancotea — Mon, 08 Jul 2013 07:29:25 GMT

http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/08/peek-vs-previous-when-to-use-each

Re: Previous or Peek function???

Mon, 08 Jul 2013 07:34:06 GMT

Hi,

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

Re: Previous or Peek function???

Gysbert_Wassenaar — Mon, 08 Jul 2013 07:41:04 GMT

See attached example

Re: Previous or Peek function???

Mon, 08 Jul 2013 08:06:44 GMT

Great, Thanks

-Janne

Re: Re: Previous or Peek function???

Tue, 01 Oct 2013 11:50:24 GMT

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 key	Period	Value in original data	Wanted value
1000-11111	Jan-13	2	2
1000-11111	Feb-13	-	2
1000-11111	Mar-13	1,5	1,5
1000-11111	Apr-13	0	0
1000-11111	May-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