Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
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?

1 Solution

Accepted Solutions
Not applicable
Author

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

View solution in original post

8 Replies
Not applicable
Author

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

Not applicable
Author

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
Author

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

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

Not applicable
Author

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

Not applicable
Author

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
Author

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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