topic Replacing missing values with previous row's value in QlikView

Replacing missing values with previous row's value

Mon, 25 Nov 2013 07:57:44 GMT

Hi there,

I have an excel table such as the following:

Product	Price
Product A	10
-	10
Product B	20
-	20

Notice that some of the values for 'Product' is missing. What I would like to do in my load statement, is to populate these missing values with the value of the above row (i.e in this case replace first '-' with Product 'A', and second '-' with 'Product B')

Is there a way in the Load statement to check if Product is null/missing, then populate with the value above?

Re: Replacing missing values with previous row's value

jonathandienst — Mon, 25 Nov 2013 08:05:20 GMT

Like this:

LOAD

If(IsNull(Product), Peek('Product'), Product) As Product,

Price

FROM ...

Or use the advanced filtering from the load wizard and use the Fill option.

HTH

Jonathan

Re: Replacing missing values with previous row's value

Anonymous — Mon, 25 Nov 2013 08:06:45 GMT

Looks like youare in need of the Peek() function.

This blog post gives lots of details:

Generating Missing Data In QlikView

Best Regards, Bill

Re: Replacing missing values with previous row's value

Mon, 25 Nov 2013 10:16:26 GMT

Thanks!

Re: Replacing missing values with previous row's value

Mon, 25 Nov 2013 10:16:44 GMT

Thanks!