topic Include null values in QlikView

Include null values

Wed, 10 Dec 2014 22:00:20 GMT

I am working a sum set analysis.

One of the fields includes null values, 0s, and 1s.

What I have so far is:

Sum({<Deal__c -={1}>} Contract_Value__c)). It = excludes 1 and has 0, but doesn't include null values.

How can i include null values for my sum set analysis?

Re: Include null values

Wed, 10 Dec 2014 22:29:34 GMT

Can you Include a Sample test QVW ? It would be easy to understand.

Re: Include null values

Wed, 10 Dec 2014 22:39:28 GMT

Unfortunately no, our company is stringent on data sharing.

But is it possible to convert null values into 0s?

That way, I can include 0 and originally null values.

Re: Include null values

Wed, 10 Dec 2014 22:48:56 GMT

Yes you can convert null values into 0's by following syntax in load script

If(Field=Null(),0,Field) as Field1

Re: Include null values

Wed, 10 Dec 2014 23:01:26 GMT

I keep getting an error, but will "NULLASVALUE Deal__c;" work as well?

Re: Include null values

Wed, 10 Dec 2014 23:06:03 GMT

During loading Data in Back end script

If(NULLASVALUE Deal__c=Null(),0,NULLASVALUE Deal__c) as NULLASVALUE Deal__c1

then you will have new field called NULLASVALUE Deal__c which have 0's in place of null

Re: Include null values

Wed, 10 Dec 2014 23:15:08 GMT

Hello, Please create the Flag for the field to check its null or not while loading.

IF( Len(TRIM(FIELDNAME)) = 0 , 0 , 1) AS NULLFLAG

Use this field on your SET Analysis

Sum( {<NULLFLAG={0}>} Contract_Value_c) --> Its Include the Null Value

Sum( {<NULLFLAG={1}>} Contract_Value_c) --> Its exclude the Null Value

If you dont want to create any flag field

Sum( {<Deal_C-={"?*"}>} Contact_Value_c ) --? Its Include the Null Value

Sum( {<Deal_C={"?*"}>} Contact_Value_c ) --? Its exclude the Null Value

Re: Include null values

rwunderlich — Wed, 10 Dec 2014 23:18:29 GMT

You can address this by using NullAsValue in the script like this:

NullAsValue Deal__c;

SET NullValue=NULL;

The nulls will be replaced by the string "NULL" which you can test for.

Without modifying the script, you can use this set modifier to select the nulls

<Deal__c -={"=len([Deal__c])=0"}>

-Rob

Re: Include null values

rwunderlich — Wed, 10 Dec 2014 23:22:09 GMT

This doesn't look like correct syntax. Can you post a more complete example script?