Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Counting null values in a table

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?

11 Replies
Not applicable
Author

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

Not applicable
Author

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.

Not applicable
Author

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

Not applicable
Author

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

Not applicable
Author

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

rohit214
Creator III
Creator III

hi

try this

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

may it helps you

thanks

rohit

Not applicable
Author

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

Not applicable
Author

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

jagan
Luminary Alumni
Luminary Alumni

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.