topic Counting Non-Null Values Across Multiple Fields in QlikView

Counting Non-Null Values Across Multiple Fields

Mon, 11 Mar 2013 14:23:53 GMT

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

Re: Counting Non-Null Values Across Multiple Fields

Gysbert_Wassenaar — Mon, 11 Mar 2013 14:30:04 GMT

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)

Re: Counting Non-Null Values Across Multiple Fields

Mon, 11 Mar 2013 15:57:37 GMT

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

Re: Counting Non-Null Values Across Multiple Fields

Gysbert_Wassenaar — Mon, 11 Mar 2013 17:25:22 GMT

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

Re: Counting Non-Null Values Across Multiple Fields

Mon, 11 Mar 2013 18:15:01 GMT

Thanks for your help.