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

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

Fri, 29 May 2015 06:26:13 GMT

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

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

vardhancse — Fri, 29 May 2015 06:32:14 GMT

Hi,

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

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

datanibbler — Fri, 29 May 2015 06:32:23 GMT

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

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

vardhancse — Fri, 29 May 2015 06:38:24 GMT

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

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

jonathandienst — Fri, 29 May 2015 06:41:11 GMT

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.

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

Fri, 29 May 2015 06:50:01 GMT

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

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

vardhancse — Fri, 29 May 2015 06:54:14 GMT

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

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

pokassov — Fri, 29 May 2015 06:59:20 GMT

Hello!

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

Fri, 29 May 2015 07:06:51 GMT

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

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

Fri, 29 May 2015 08:20:06 GMT

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

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

Fri, 29 May 2015 09:02:05 GMT

Set NullInterpret = '';

TestData:

NoConcatenate

LOAD * INLINE [

ID, A, B, C, D

1,a,b,,

2,,b,c,d

3,a,,,

];

//******************

// Variant 1

LEFT JOIN(TestData)

LOAD ID, if(IsNull(A),1,0)+if(IsNull(B),1,0)+if(IsNull(C),1,0)+if(IsNull(D),1,0) as MissingCount1

Resident TestData;

//******************

// Variant 2

CheckColumns:

NoConcatenate

LOAD * INLINE [

CheckColumns_Table, CheckColumns_Key, CheckColumns_Column

TestData,ID,A

TestData,ID,B

TestData,ID,C

TestData,ID,D

];

Tmp1:

NoConcatenate

LOAD Distinct CheckColumns_Table as Tmp1_Table, CheckColumns_Key as Tmp1_Key

Resident CheckColumns;

for vRow1 = 1 to NoOfRows('Tmp1')

LET vTable = peek('Tmp1_Table', $(vRow1)-1, 'Tmp1');

LET vKey = peek('Tmp1_Key', $(vRow1)-1, 'Tmp1');

Tmp2:

NoConcatenate

LOAD CheckColumns_Column as Tmp2_Column

RESIDENT CheckColumns;

LET vTextMissingCount2 = '';

FOR vRow2 = 1 to NoOfRows('Tmp2')

LET vCheckCol = peek('Tmp2_Column', $(vRow2)-1, 'Tmp2');

LET vTextMissingCount2 = vTextMissingCount2 & 'if(IsNull($(vCheckCol)),1,0)+';

NEXT vRow2

LET vTextMissingCount2 = Mid(vTextMissingCount2,1,Len(vTextMissingCount2)-1);

LEFT JOIN($(vTable))

LOAD $(vKey), $(vTextMissingCount2) as MissingCount2

Resident $(vTable);

DROP TABLE Tmp2;

NEXT vRow1

//******************

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

Fri, 29 May 2015 09:31:39 GMT

Thank you so much for the help! This one solve my problem.