topic how to count null value in a field in QlikView

how to count null value in a field

rohit214 — Tue, 29 Nov 2011 06:54:51 GMT

hi all

i have feild name invadate

i want to count null value only

please help me

thanks &regards

rohit

Re: how to count null value in a field

Sokkorn — Tue, 29 Nov 2011 07:23:05 GMT

Hi rohit214,

In load script let try this

IF(LEN(TRIM([invadate]))<0,1,0)     AS NullFlage

HTH and let me know.

Regards,

Sokkorn Cheav

Re: how to count null value in a field

rohit214 — Tue, 29 Nov 2011 07:30:24 GMT

hi Sokkorn

thank for your reply it shoes me 15 but the no of null values is 12

it is not workinf fine

rhanks

rohit

how to count null value in a field

jagan — Tue, 29 Nov 2011 07:31:44 GMT

Hi Rohit,

Hope this helps you

=Sum(if(isnull(invdate) OR Len(Trim(invdate)) = 0, 1, 0))

Or try the function

NullCount(invdate)

Regards,

Jagan.

how to count null value in a field

SunilChauhan — Tue, 29 Nov 2011 07:36:49 GMT

count( if(isnull(invdate),invdate))

count( if(len(invdate)=0,invdate))

how to count null value in a field

rohit214 — Tue, 29 Nov 2011 07:38:05 GMT

hi jagan

i want result in text object

null value=12

but your solution is giving me 15

which is not correct

thanks

rohit

how to count null value in a field

rohit214 — Tue, 29 Nov 2011 07:40:10 GMT

hi sunil

your solution gives me 0

thanks

rohit

how to count null value in a field

SunilChauhan — Tue, 29 Nov 2011 07:42:58 GMT

can you attached the sample.qvw file after reloading your excel

how to count null value in a field

jagan — Tue, 29 Nov 2011 07:46:10 GMT

Hi Rohit,

Will you upload the QVW file which you are working. When I loaded your excel file in QVW i am getting 0 by using the expression

=Sum(if(isnull(invdate) OR Len(Trim(invdate)) = 0, 1, 0))

When I referred the table values i didn't found any missing or null values in it. I think the empty are not loaded during the loading.

Regards,

Jagan.

how to count null value in a field

jagan — Tue, 29 Nov 2011 07:50:11 GMT

Hi,

When I manually given single space in the empty cells in excel file, i got the count which you expected i.e,.12

by using the following expression

=Sum(if(isnull(invdate) OR Len(Trim(invdate)) = 0, 1, 0))

=MissingCount(invdate)

Regards,
Jagan.

Re: how to count null value in a field

Sokkorn — Tue, 29 Nov 2011 08:06:49 GMT

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

Re: how to count null value in a field

SunilChauhan — Tue, 29 Nov 2011 08:22:14 GMT

see the attached file

hope this helps

Re: how to count null value in a field

rohit214 — Tue, 29 Nov 2011 08:36:01 GMT

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

how to count null value in a field

Sokkorn — Tue, 29 Nov 2011 08:45:06 GMT

Hi rohit,

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

Regards,

Sokkorn Cheav

Re: how to count null value in a field

Miguel_Angel_Baeyens — Tue, 29 Nov 2011 09:04:32 GMT

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:

Go to Startup, Run, C:\Windows\SysWOW64\odbcad32.exe
Add a new Excel (xlsx) driver and point it to your file, name it Excel1
Check the box "Force 32 bits" in the script editor.
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