Null value count by fields in table

[miranrai]

I have a requirement to calculate the number of of null values per column wise of a particular table by company code.

Data is like:

(Table has 45-50 Columns)

Company	LOC	Supp	GPR
1	A	UI	x
1		KI
2	D		y
2	F
3			z

Result:

Company	LOC	Supp	TAX
1	1	0	1
2	0	2	1
3	1	1	0

is this possible in qlikview?

Please help.

[#2]

You can use if(isnull(fieldname), 'NULL') while loading the table and then count NULL in the expression. Hope this helps.

[miranrai]

i have already calculated null values while loading table.. I am not sure how to put it in formula.. when i put formula as

if($field='LOC' count(null_loc),

if($field='Supp' count(null_Supp), 0))

it gives me total no of null values in table. its not calculating by field.

[sinanozdemir]

Hi Mira,

I used set analysis:

I am also attaching the qvw.

[prabhu0505]

Dimension:

Company

Expressions:

NullCount(LOC)

NullCount(Supp)

NullCount(GPR)

should work.

[prabhu0505]

Set NullInterpret = '';

Set NullInterpret = ;

Forgot to mention, add this to your script.

[#7]

I could be missing something and do not want to state the obvious, but on each expression tab, could you not select the radio buttom below Expression total and select the option to count either null or non-null counts?

[sinanozdemir]

I think that's for the total mode and it adds totals for the whole table:

[prabhu0505]

PFA.