Handling Null Values in GroupBy Sum Operations

[JuMo]

Hello

I have three fields that may sometimes contain null values. When I perform a group by operation in my script with 'Sum(NumberSumX)', the result returns 0 even when the values are null. I want the sum of null values to be null as well.

Here's my current script:

Load

   Dimension,

   Sum(Number1) as NumberSum1,

   Sum(Number2) as NumberSum2,

   Sum(Number3) as NumberSum3

From ...

Group By Dimension;

I've seen solutions using a where statement, but they seem to only work with a single field. I'm having trouble adapting it to work with multiple fields. In my case, any of the NumberX fields can be null while the others are not.

How can I modify my script to achieve the desired result?

[robert_mika]

Did you try to add IF statemt that if SUM()=null(), null()...

[QFabian]

Hi @JuMo , what about something like this, doing the sums and then replacing them with null when is 0 :

Data_Aux:

Load

   Dimension,

   Sum(Number1) as NumberSum1_Aux,

   Sum(Number2) as NumberSum2_Aux,

   Sum(Number3) as NumberSum3_Aux

From ...

Group By Dimension;

Data:

Load

   Dimension,

    if(NumberSum1_Aux = 0, null(), NumberSum1_Aux) as NumberSum1,

    if(NumberSum2_Aux = 0, null(), NumberSum2_Aux) as NumberSum2,

    if(NumberSum3_Aux = 0, null(), NumberSum3_Aux) as NumberSum3

Resident Data_Aux;

drop table Data_Aux;

[JuMo]

My first idea was

If(IsNull(Number1),Null(),Sum(Number1)) as NumberSum1,
If(IsNull(Number2),Null(),Sum(Number2)) as NumberSum2,
If(IsNull(Number3),Null(),Sum(Number3)) as NumberSum3

but i get an "Invalid expression"

But that's not exactly what you are proposing, I have tested your solution

If(IsNull(Sum(Number1)),Null(),Sum(Number1)) as NumberSum1,
If(IsNull(Sum(Number2)),Null(),Sum(Number2)) as NumberSum2,
If(IsNull(Sum(Number3)),Null(),Sum(Number3)) as NumberSum3

And I only get 0,  no Null for sums of Null elements

[JuMo]

I cannot do that because sometime the sum is 0

[rwunderlich]

The challenge is that Sum() treats null input as 0 by design.  If you want to report null when all values of a dimension are null, you could do something like below. I'm using a "shadow" field where concat() collects the values. If the length of the shadow field is 0, then everything was null (or blank).

// Generate some test data

Data:

Load Dimension,

  EmptyIsNull(Num1) as Num1,

  EmptyIsNull(Num2) as Num2 

Inline [

Dimension, Num1, Num2

A, 2, 4

A, 4, 5

B, , 8

B, , 7

C, 3,

C, 4, 

D, 12, 34 

];

Totals:

Load

  Dimension,

  if(Len(AllNum1) = 0, null(), SumNum1) as SumNum1,

  if(Len(AllNum2) = 0, null(), SumNum2) as SumNum2

;

LOAD 

  Dimension,

  Concat(Num1) as AllNum1,

  Sum(Num1) as SumNum1,

  Concat(Num2) as AllNum2,

  Sum(Num2) as SumNum2

Resident Data

Group By Dimension;

-Rob
http://www.easyqlik.com
http://masterssummit.com
http://qlikviewcookbook.com

[Or]

Plenty of good suggestions, but I'll throw in one more:

Sum(FieldName) * If(NullCount(FieldName)<>Count(FieldName),1)

The sum stays clean, and we multiply this by 1 if the count of nulls is not identical to the count of rows (including nulls). If the counts are identical, we multiply this by the else condition which, since it is not provided, is null(), thus nulling the entire expression. 

[rwunderlich]

@Or Great idea! I think there is a bug though. Count() returns count of non-null values, so your condition is always true. I'll amend my solution to use a modified version of yours as:

Totals:

LOAD 

  Dimension,

  Sum(Num1) * If(Count(Num1) > 0, 1, null()) as SumNum1,

  Sum(Num2) * If(Count(Num2) > 0, 1, null()) as SumNum2

Resident Data

Group By Dimension

;

-Rob

[Or]

@rwunderlich Right. I misread the documentation, and I'm away from my Qlik so no testing. 

I've always been a fan of using simple mathematical operations to avoid complicated logical ones, when it's feasible. I do strongly recommend documentation the logic, though, because I've noticed that some developers struggle to read this sort of thing. Including the explicit Null() in the else isn't necessary, but it does make for easier reading I suppose. 

[rwunderlich · Accepted solution]

Come to think of it, this works as well and may be a bit clearer.

Totals:

LOAD 

  Dimension,

  If(Count(Num1) > 0, Sum(Num1), null()) as SumNum1,

  If(Count(Num2) > 0, Sum(Num2), null()) as SumNum2

Resident Data

Group By Dimension;

-Rob