topic Replacing null value with previous row value in QlikView

Replacing null value with previous row value

Wed, 02 Feb 2011 18:23:45 GMT

I am trying to replace null values in a table with the value in the previous row. The images below should give a better idea of where I am currently(image1) and the result(image2) desired. The simplest way to do it seemed to be to use the peek function(refer caption in image1). However, I have had no success so far. I tried both isnull(),len(fieldname) with similar results. Qlikview seems to be reading the field 'Type' correctly, len(Type) and isnull(Type) both seem to indicate the '-' in the Type field are null value. I don't know if I am using the function incorrectly. Any help would be highly appreciated.

Replacing null value with previous row value

matt_crowther — Wed, 02 Feb 2011 18:34:20 GMT

You'll have to apply this to your situation but it should be easy enough:

=if(isnull(sum(Sales),above(sum(Sales),1),sum(Sales))

Hope that helps - replace 'sum(Sales)' with your expression.

* It can behave strangely with complex dimensions and pivot tables.

Matt - Visual Analytics Ltd

Replacing null value with previous row value

Wed, 02 Feb 2011 18:40:30 GMT

=if(Len(Type) = 0 ,Above(NewType), NewType)

Above function - Returns the value of expression evaluated with the chart's dimension values as they appear on the row above the current row within a column segment in a table

Please try this and see if it works for you.

Replacing null value with previous row value

Anonymous — Wed, 02 Feb 2011 18:41:16 GMT

see my post here:
http://community.qlik.com/media/p/141922.aspx

Replacing null value with previous row value

Wed, 02 Feb 2011 19:53:51 GMT

Thanks to all you guys who responded right away.' Above' sounds like it should logically do the trick.

I tried the above function like Matt and Priyadarshini suggested. This is what I got.

Am I missing something ? Type and NewType(before the above function is applied in the chart) hold the same values initially. If not, then it seems like Matt suggested, it is acting funky with my dimensions.

Micheal, I am trying to use your method to see if it solves my issue. Thanks again, guys.

Replacing null value with previous row value

Wed, 02 Feb 2011 21:44:31 GMT

if(Len(Type) = 0 ,Above(Type), NewType)

Please try this.

Replacing null value with previous row value

Thu, 24 Mar 2011 08:36:41 GMT

Hello Priyadarshini,

My question is in the above figure what have shown -I want to to sum all the values 1 in the "len(Type)" column.I use thye sum function but that does not match with my requirement.

Pleasee help me if there are any idea about this summation problem.

Thank you.