topic Re: Null value count by fields in table in QlikView

Null value count by fields in table

miranrai — Tue, 04 Aug 2015 15:21:42 GMT

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.

Re: Null value count by fields in table

Tue, 04 Aug 2015 16:14:32 GMT

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

Re: Null value count by fields in table

miranrai — Tue, 04 Aug 2015 16:23:21 GMT

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.

Re: Null value count by fields in table

sinanozdemir — Tue, 04 Aug 2015 17:07:04 GMT

Hi Mira,

I used set analysis:

I am also attaching the qvw.

Re: Null value count by fields in table

prabhu0505 — Tue, 04 Aug 2015 17:13:01 GMT

Dimension:

Company

Expressions:

NullCount(LOC)

NullCount(Supp)

NullCount(GPR)

should work.

Re: Null value count by fields in table

prabhu0505 — Tue, 04 Aug 2015 18:02:17 GMT

Set NullInterpret = '';

Set NullInterpret = ;

Forgot to mention, add this to your script.

Re: Null value count by fields in table

Tue, 04 Aug 2015 19:33:00 GMT

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?

Re: Null value count by fields in table

sinanozdemir — Tue, 04 Aug 2015 19:40:06 GMT

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

Re: Null value count by fields in table

prabhu0505 — Wed, 05 Aug 2015 00:03:20 GMT

PFA.