Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Include null values

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?

Tags (3)
1 Solution

Accepted Solutions
Not applicable

Re: Include null values

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

8 Replies
Not applicable

Re: Include null values

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

Not applicable

Re: Include null values

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.

Not applicable

Re: Include null values

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

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

Not applicable

Re: Include null values

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

Not applicable

Re: Include null values

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

Not applicable

Re: Include null values

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

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

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

Community Browser