Counting Non-Null Values Across Multiple Fields

[#1]

I am trying to count the number of fields with a value across multiple fields.  If I was using excel, I would use the either of the following formulas:

• IF(Field1=0,0,1)+IF(Field2=0,0,1)+IF(Field3=0,0,1)
• IF(Field1="",0,1)+IF(Field2="",0,1)+IF(Field3="",0,1)

Using either of these formulas, the null values were not counted.

This does not work in Qlikview.  When I enter either of the above

formulas in the script, it will always count the null values.  I have tried the two formulas above along with the following:

IF(Field1='',0,1)+IF(Field2='',0,1)+IF(Field3='',0,1) as OutOf

IF(Field1=null(),0,1)+IF(Field2=null(),0,1)+IF(Field3=null(),0,1) as OutOf

Is there a way to not count null values in the formula?  How do I approach this in Qlikview to get the results I would from excel?  Any help would be greatly appreciated

Thanks

Brian

[Gysbert_Wassenaar]

To determine if a field is null or an empty string you can do it with if(len(trim(MyField))=0,1,0) as IsNull

In the script you can calculate aggregated counts with something like count(MyField)-nullcount(MyField) as MyCount. (don't forget the group by clause)

In expressions you can use count({<MyField={'*'}>}MyField)

[#3]

I tried the script formula: count(Field1)-nullcount(Field1) as MyCount and received an Invalid Expression on the load.  I am guessing this is because I am missing the GROUP BY clause.  I am not very familiar with how this clause works.  Can you provide an example?

Also, I have attached a copy of the Qlikview Script and the output I would typically get with Excel, as an example.

Thanks for you help

[Gysbert_Wassenaar · Accepted solution]

Ah, like that. Try: -rangesum(isnum(Field1),isnum(Field2),isnum(Field3)) as Count

If the contents of Fieldx is a number then isnum returns -1, otherwise 0. We add those and multiply with -1

[#5]

Thanks for your help.