topic Counting null values in a table in QlikView

Counting null values in a table

Tue, 29 Nov 2011 03:39:31 GMT

Hi everyone,

I am trying to use the count function to count the number of orders that do not have null value in its invoice date. I have tried a lot of what was already on the community like:

=cout(distinct if(len(invdate)>0, key));

= count( {$} KEY)

= count(distinct if(isnull(INVDATE)=0, key));

I am running out of ideas. Yet if I use the expression: = IF(INVDATE='', NOINVOICE), that works well proving that the QV identifies the missing date.

Anyone?

Re: Counting null values in a table

Tue, 29 Nov 2011 03:57:22 GMT

Hi adjacentrock,

Try this expression,

count(distinct if(len(trim(invdate))>0, invdate))

I tested, it works for me.

The trim() is important in this expression, because even if the invdate only contains some spaces, the len() will return a nonzero value. So trim() removes the spaces in empty invdate if it has any.

Regards

Counting null values in a table

Tue, 29 Nov 2011 04:07:13 GMT

Hi,

thanks but it still doesnt work, also, the heading of my question is not really correct as I am trying to count records that have null values in their invoice date.

Re: Counting null values in a table

Tue, 29 Nov 2011 04:12:10 GMT

Can you upload your data file with merely the invoice date field? Just a fraction should be fine.

Counting null values in a table

Tue, 29 Nov 2011 04:25:25 GMT

silly question where do you insert qv docs in qlikcommunity? am sending it to you by email in the mean time

Re: Counting null values in a table

Tue, 29 Nov 2011 04:30:35 GMT

From: quickview

Sent: Tuesday, 29 November 2011 3:12 PM

To: Danny Rock

Subject: - Re: Counting null values in a table

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Counting null values in a table

created by quickview<http://community.qlik.com/people/quickview> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/168589#168589

Re: Counting null values in a table

rohit214 — Tue, 29 Nov 2011 04:48:20 GMT

try this

=if(isnull(invdate)='-1',count (invdate))

may it helps you

thanks

rohit

Re: Counting null values in a table

Tue, 29 Nov 2011 05:06:02 GMT

Not working either, it just ignores every order in the count function.

From: rohit214

Sent: Tuesday, 29 November 2011 3:48 PM

To: Danny Rock

Subject: - Re: Counting null values in a table

QlikCommunity<http://community.qlik.com/index.jspa>

Re: Counting null values in a table

created by rohit214<http://community.qlik.com/people/rohit214> in Development (QlikView Desktop) - View the full discussion<http://community.qlik.com/message/168579#168579

Counting null values in a table

Tue, 29 Nov 2011 05:06:04 GMT

No its not working either. It is so strange because if i put the expression:

IF(ISNULL(INVOICEDATE)='-1','NO INVOICE'),

it knows that it is empty and shows the NO INVOICE where there really is not any invoice, but then when is put the same condition in the the count function, it ignores all in the count

Counting null values in a table

jagan — Tue, 29 Nov 2011 05:43:05 GMT

Hi,

Hope this helps you in solving your problem

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

Also this will improve the performance than using the count.

Regards,

Jagan.

Re: Counting null values in a table

Tue, 29 Nov 2011 06:14:44 GMT

Hi there,

Here is the count number of your INVOICEDATE records:2033.

I tested in your given qvw example file.

(The field name in the expression should be as same as the field name in the script. In this case it is upcase.)

The expression is as follows:

count(DISTINCT if(len(trim(INVOICEDATE))>0,INVOICEDATE))

Regards

Counting null values in a table

Tue, 29 Nov 2011 06:17:54 GMT

Thanks everyone, the problem was not the expression, but a case of one to many which I did not realise, so some orders had more than one invoice and when the count inluded it I didnt understand why as on the table, the order showed a blank field in the invoice, but after checking, I found out that it some of them had more than one invoice number but showed it as null on the table.....

Despite the loss of time I must admit that I did learn a lot from all you guys