Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Previous or Peek function???

See attached example


talk is cheap, supply exceeds demand
6 Replies

Re: Previous or Peek function???

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
luciancotea
Valued Contributor

Re: Previous or Peek function???

Not applicable

Re: Previous or Peek function???

Hi,

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

Re: Previous or Peek function???

See attached example


talk is cheap, supply exceeds demand
Not applicable

Re: Previous or Peek function???

Great, Thanks

-Janne

Not applicable

Re: Re: Previous or Peek function???

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

Community Browser