how to count null value in a field

[rohit214]

hi all

i have feild name invadate

i want to count null value only

please help me

thanks &regards

rohit

[Sokkorn]

Hi rohit,

I think every data source should have primary key; something like this:

ID	invdate
1	12
2	213
3
4
5
6
7	4543
8	43
9	1
10
11	21
12
13
14	4652
15	433
16
17	6213
18
19
20
21	23
22	3
23	56
24	486
25	44
26
27	25

So when I use this expression : =SUM(IF(IsNull(invdate) OR LEN(TRIM(invdate))<0,1,0)) then it return me 12.

I'm not sure this one is you looking for or not.

Regards,

Sokkorn Cheav

[SunilChauhan]

see the attached file

hope this helps

[rohit214]

hi all,

thamks for your answer

may be i am wrong counting null values in a single field is not possible

you have to connect tha tfield with another field

thanks &regards

rohit

[Sokkorn]

Hi rohit,

Does your case is closed? Please mark it as complete if you find a solution.

Regards,

Sokkorn Cheav

[Miguel_Angel_Baeyens]

Hi Rohit,

If you load the XLSX file using the "Table Files" button in the script editor, this will load only non-empty cells from Excel, because you only have one column. It has nothing to do with QlikView but with the Microsoft Excel driver. If you had two colums, the nulls will indeed be counted. What you can do instead is to create an ODBC driver entry to read files using it instead of the button.

Follow these steps:

1. Go to Startup, Run, C:\Windows\SysWOW64\odbcad32.exe
2. Add a new Excel (xlsx) driver and point it to your file, name it Excel1
3. Check the box "Force 32 bits" in the script editor.
4. Then use the following script:

ODBC CONNECT32 TO [Excel1;DBQ=C:\Files\count null.xlsx];
TableWithNulls:
LOAD invdate,
           If(Len(invdate) = 0, RangeSum(1, Peek('NullCounter')), RangeSum(0, Peek('NullCounter'))) AS NullCounter;
SQL SELECT *
FROM `C:\Files\count null.xlsx`.`Sheet1$`;

This will return each of the lines with a counter that will be increased each time invdate has a null/empty value. The total number of nulls will be the Max(NullCounter).

Hope that helps.

Miguel