How to count null values (per rows) from a Straight Table?

[#1]

Hi there!

Can someone help me count null values in my Straight Table? Need to know what expressions to write if I want to count the total number of missing information of each transaction in my table.

Below is my example:

ID	A	B	C	D	MissingCount
1	a	b	null	null	2
2	null	b	c	d	1
3	a	null	null	null	3

What would be my expression for the MissingCount? Your help is much appreciated. Thank you.

regards,

Bea

[vardhancse]

Hi,

in qlikview table its showing as null, or else you are replacing null values with the text 'null'

[datanibbler]

Hi Bea,

missing values are always tricky to deal with.

You'll have to try out a few things. On the GUI, it might work with either the LEN() function or ISNULL() or ISNUM() or ISTEXT (wrapped in a NOT()).

If all that doesn't work, you'll have to find a solution in the script to replace empty spaces with some text like "no data" so you'll have something specific to count.

HTH

[vardhancse]

Hi,

correct in qlikview its not possible to drill down based on nulls or expression based on nulls.

that is why I asked are you replacing the blanks will the text 'null', if then we can add in expression and can get the count. a part from that we can not get the count of blanks

[jonathandienst]

You can count null values using

Sum(If(Len(field) = 0, 1) or Sum(If(IsNull(field), 1, 0)

or use NullAsValue field; in your load script.

But you cannot count missing values. The former are actual values that evaluate to null, the latter simply do not exist and there is no direct way to count them - they are just empty holes in your table.

[#6]

Hi Sasi,

Thanks for the information, however, my data source here is really null or blank data. Then I uncheck the Suppress Zero-Values and Put "NULL" on the Null Symbol and Missing Symbol on Presentation Tab. Can I consider that as value already for me to count? Or do I still need to do an update script for each blank items with any text?

Thanks for the help.

regards,
Bea

[vardhancse]

Hi,

You are trying to just represent the null values in front end of dashboard. But that does not work out.

Instead we need to replace all null values to some text like 'no data' then in expression we can give some conditions like

if(date='null',Sum(Distinct(Date)))

So that as we replaced the null values with the text 'null' we can use in our conditions

[pokassov · Accepted solution]

Hello!

[#9]

You may Write IF condition and find the NULL value and replace it with some text or number and the count it next.

Or If you are sure that there is at-least one column which doesn't hold NULL value then may write something like

if(FieldhavingNULL='null',COUNT(Distinct FIELDNOTHAVINGNULL))

[#10]

Hi,

I already update all my blank data into 'NO DATA' text value, however, it haven't get the correct count.

Based on my Table example above, on ID: 1, how can I get the MissingCount = 2 if my C and D has 'NO DATA'  on it. Thanks a lot.

-Bea