Counting null values in a table

[#1]

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?

[#2]

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

[#3]

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