Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
IMPORTANT security patches for GeoAnalytics Server available to download: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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

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:

 

IDABCDMissingCount
1abnullnull2
2nullbcd1
3anullnullnull3

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

regards,

Bea

1 Solution

Accepted Solutions
pokassov
Specialist
Specialist

11 Replies
vardhancse
Specialist III
Specialist III

Hi,

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

datanibbler
Champion
Champion

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
Specialist III
Specialist III

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
Partner
Partner

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.

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein
Not applicable
Author

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
Specialist III
Specialist III

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
Specialist
Specialist

Hello!

Not applicable
Author

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))

Not applicable
Author

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