topic Re: How to handle the null values in the script in QlikView

How to handle the null values in the script

Sat, 22 Sep 2012 10:08:48 GMT

Hi,

I am loading the data via OLE DB from MS Access Views. the Views containd basic components of net sales calculation like price, discounts, volume.. the Net sales calculation for each row is done in the script by preceading Load statement, so the synthax of my script is:

Load

Net_Price,

Volume,

Discount1,

Discount2,

Product,

/other descriptive dimensons/,

Net_Price * Volume * (1-Discount1) *(1-Discount2) as Net_Sales

SQL Select *

From MyAccessView

the problem is that Discounts take sometimes null values and for such records, Net_Sales is as well null what is wrong, as this null should have been treated in the formula as zero... I handled this in discount definition as follows:

if(Discount1='',0,Discount1) as Discount__1

The problem is that I can not use Discount__1 name when defining Net_Sales, I can incorporate if... formulas in the Net_Sales definition, but having around 10 kinds of discpounts, it is going to be a looooong formula. Can I handle this differently?

I was trying to handle this in Acces Views by usnig NZ(Discount1,0) formula, but then QV does not see the Views any more which as I got to know is because it does not recognise NZ() function :-((

Can you please help?

Re: How to handle the null values in the script

hic — Sat, 22 Sep 2012 13:18:25 GMT

use

Net_Price * Volume * (1-RangeSum(Discount1)) *(1-RangeSum(Discount2)) as Net_Sales

See more on http://community.qlik.com/docs/DOC-3155

HIC

Re: How to handle the null values in the script

whiteline — Sat, 22 Sep 2012 16:12:31 GMT

Hi.

Also, to check if the value is null you should use isnull(Discount1) function.

Re: How to handle the null values in the script

Sat, 22 Sep 2012 20:01:15 GMT

Thanks a lot... this was working of course, but anyway I must have resigned from my beginning approach.

When I simply connected my query to QV, I was getting script load errors with really no reasone. After some tests I concluded it must have something to do with many formulas being in the query, because when I connected the table containing the same data as in my big Access Query (so simply I changed the type of my query from "select" to "Table create"), then all the data were loaded within a second.

So, As I load the data anyway from Query to Table before refreshing QV, I used NZ() formulas in Query to assure no Null values...

Answering Whiteline - when I used the formula you suggest, then I was still getting empty values, so probably the values were not null as per its QV definition