8 Replies Latest reply: Dec 10, 2014 6:22 PM by Rob Wunderlich

# 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?

• ###### Re: Include null values

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

• ###### 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.

• ###### 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

• ###### Re: Include null values

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

• ###### Re: Include null values

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

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

• ###### 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

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