Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

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

Re: Counting null values in a table

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

Counting null values in a table

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

Re: Counting null values in a table

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

Not applicable

Counting null values in a table

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

Not applicable

Re: Counting null values in a table

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
Contributor III

Re: Counting null values in a table

hi

try this

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

may it helps you

thanks

rohit

Not applicable

Re: Counting null values in a table

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

Counting null values in a table

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

MVP
MVP

Counting null values in a table

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.

Community Browser