Solved: Re: Null Count - Qlik Community

bhavvibudagam · ‎2017-12-20

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

vishsaggi · ‎2017-12-20

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 )

View solution in original post

sunny_talwar · ‎2017-12-20

May be this

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

sunny_talwar · ‎2017-12-20

My bad, this is the right syntax

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

Anil_Babu_Samineni · ‎2017-12-20

Or this?

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

Best Anil, When applicable please mark the correct/appropriate replies as "solution" (you can mark up to 3 "solutions". Please LIKE threads if the provided solution is helpful

sunny_talwar · ‎2017-12-20

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

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

sunny_talwar · ‎2017-12-20

Not working? Which one is not working?

bhavvibudagam · ‎2017-12-20

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

This expression is not working getting 0 instead of 17

sunny_talwar · ‎2017-12-20

My bad, needed to count ID instead of value

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

Anonymous · ‎2017-12-20

Hi,

Try

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

Regards.

trdandamudi · ‎2017-12-20

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