topic Re: Null Count in QlikView

Null Count

bhavvibudagam — Wed, 25 Nov 2020 16:16:04 GMT

Hi Experts,

I am trying to calculate the Null values present in Value field. Here the nullcount under Value is 17.

Please provide the solution to calculate the Nullcount under value field apart from below two methods.

=Count( IF(ISNULL(Value) OR LEN(trim(Value))=0 OR Value=0,1))

=NullCount(Value)

ID	Value
1	-1301
2
3
4	7
5
6	63
7
8	77
9
10
11
12
13
14
15
16	71
17
18
19
20	32
21
22	45
23
24	38
25
26	41

Thanks in advance

Re: Null Count

sunny_talwar — Wed, 20 Dec 2017 13:34:03 GMT

May be this

Sum(-Len(Trim(Value)) = 0)

Re: Null Count

sunny_talwar — Wed, 20 Dec 2017 13:35:34 GMT

My bad, this is the right syntax

=-Sum(Len(Trim(Value)) = 0)

Re: Null Count

Anil_Babu_Samineni — Wed, 20 Dec 2017 13:38:01 GMT

Or this?

=Count({<Value -= {"=Len(Value)>0"}>} Value)

Re: Null Count

sunny_talwar — Wed, 20 Dec 2017 13:39:02 GMT

If Value is truly null, then this won't work.... but this can

=Count({<ID = {"=Len(Value) = 0"}>} Value)

Re: Null Count

sunny_talwar — Wed, 20 Dec 2017 13:54:17 GMT

Not working? Which one is not working?

Re: Null Count

bhavvibudagam — Wed, 20 Dec 2017 13:57:40 GMT

=Count({<ID = {"=Len(Value) = 0"}>} Value)

This expression is not working getting 0 instead of 17

Re: Null Count

sunny_talwar — Wed, 20 Dec 2017 13:58:43 GMT

My bad, needed to count ID instead of value

=Count({<ID = {"=Len(Value) = 0"}>} ID)

Re: Null Count

Anonymous — Wed, 20 Dec 2017 16:20:38 GMT

Hi,

Try

=Count(if(IsNull(Value)or Value='',1))

Regards.

Re: Null Count

trdandamudi — Wed, 20 Dec 2017 20:44:00 GMT

One more way:

If you are sure the Value field is always a number then the following will work:

=Count(If(Not IsNum(Value),1))

Re: Null Count

vishsaggi — Wed, 20 Dec 2017 20:54:50 GMT

May use a NullAsValue and count on that Field. Like

NULLASVALUE Value;

SET NullValue = '<NoData>';

YourLoadStatement. Like

LOAD ID, Value

FROM yoursource;

Then use like

= Count({< Value = {'<NoData>'} >} ID )

Re: Null Count

zebhashmi — Wed, 20 Dec 2017 20:58:21 GMT

ITHINK

Count(aggr({<Value={0}>}ID))

Re: Null Count

bhavvibudagam — Thu, 21 Dec 2017 03:26:32 GMT

Hi,

The expression is showing error.

Thanks,

Re: Null Count

devarasu07 — Thu, 21 Dec 2017 03:45:44 GMT

Hi,

is there any issue using below set analysis method to calculate null count? would be able to share the expected output.

=Count({<ID = {"=Len(trim(Value))=0"}>} Value)

Re: Null Count

bhavvibudagam — Thu, 21 Dec 2017 03:57:29 GMT

=Count({<ID = {"=Len(trim(Value))=0"}>} ID) Its working when we use Value then getting 0 as output.

I got the expected output. I am looking for different Methods for NullCount calculation.

Thanks every one for providing all the Different Approaches for the Null Count Calculation.