topic Re: Change Null Values to a Value in QlikView

Change Null Values to a Value

Wed, 24 Oct 2012 10:06:23 GMT

I use a straight table to list our outstanding quotes.

Date, Quote No, Description, Value etc.

I use an expression to display the sum([Quote Value]), but when this value is null or 0.00 none of the quote line displays, which im guessing is down to the expression not being able to sum on a null or 0 value hence dropping the line altogether.

What would the best way to work around this? I'd like to still show the line even if the value was set to 0.01 so the user knows this quote needs to be updated.

I have experimented with

if(OfferAmount = null() or = 0.00000, 0.01, OfferAmount) as [Quote Amount],

this doesn't seem to work, but im also open to other sugestions of displaying the row data.

Any help is greatly apreciated.

Re: Change Null Values to a Value

Wed, 24 Oct 2012 10:15:40 GMT

You are correct in assuming that QlikView doesn't show these, its not that it can't do it, it is that it is correctly designed not to do it.

On a straight table you could add in an expression to count an ID, then hide the column, because QlikView should then show you the zero value lines.

For example: add in something like Count([Quote ID]) as an additional expression.

Then go to the presentation tab of the chart, select the Count expression and check the box (Hide Column), so the column is now hidden but should still be active in terms of forcing zero and null values to display.

Re: Change Null Values to a Value

Wed, 24 Oct 2012 10:27:36 GMT

Thats pefect, and a much more straightforward way of thinking.

Works just as I need. Thank you for your help.

Re: Change Null Values to a Value

Wed, 24 Oct 2012 10:29:07 GMT

My pleasure.

Re: Change Null Values to a Value

Wed, 24 Oct 2012 11:12:45 GMT

After some investigation this works for 90% of the data which is great, but is creating duplicates for a lot of the data.

Would you know of anything similar in setting a value here

if(OfferAmount = null() or = 0.00000, 0.01, OfferAmount) as [Quote Amount],

Re: Change Null Values to a Value

Wed, 24 Oct 2012 12:00:18 GMT

First, if its producing duplicate rows then it means you are probably counting and ID or some other value from the wrong table because it should not be duplicating rows.

On the expression you have, you could try something like:

=Sum(If(ISNULL(OfferAmount) OR Round(OfferAmount)=0,0.01,OfferAmount))

The above expression could be put straight into your table rather than in the load script, if want to do this in your load script then:

IF(ISNULL(OfferAmount) OR Round(OfferAmount)=0,0.01,OfferAmount) AS [Quote Amount]

An obvious warning though, each null value is now being converted to 1 cent (penny, centime etc) so if you're dealing with large volumes of data then you are naturally skewing the total values reported.

Re: Change Null Values to a Value

Wed, 24 Oct 2012 12:28:42 GMT

Thats great, thanks, the quote offer should always be a penny if the true quote value is unknow, this will be used to highlight quote errors.

Thank you for your help, thats solved the issue.

Re: Change Null Values to a Value

hic — Wed, 24 Oct 2012 19:54:24 GMT

Tech Brief of QlikView NULL handling with lots of tips on how to solve problems.

HIC