Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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 )
May be this
Sum(-Len(Trim(Value)) = 0)
My bad, this is the right syntax
=-Sum(Len(Trim(Value)) = 0)
Or this?
=Count({<Value -= {"=Len(Value)>0"}>} Value)
If Value is truly null, then this won't work.... but this can
=Count({<ID = {"=Len(Value) = 0"}>} Value)
Not working? Which one is not working?
=Count({<ID = {"=Len(Value) = 0"}>} Value)
This expression is not working getting 0 instead of 17
My bad, needed to count ID instead of value
=Count({<ID = {"=Len(Value) = 0"}>} ID)
Hi,
Try
=Count(if(IsNull(Value)or Value='',1))
Regards.
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))