How to return queries where there is no date....

[#1]

Hi All,

I am new to Qlikview, so apologies if my question is basic....

I am writing a query that will return cases that have no dates completed, ie: the date field is blank when there should be a date there.

What should the expression be to make this work (for a value, this would be 'null' as far as I understand, but not sure about dates).

Any help would be appreciated!

Si

[Miguel_Angel_Baeyens]

Hello Si,

It depends on your database and perhaps, odbc driver. Some Microsoft SQL servers return '01/01/1753' as default value for dates where those fields are not populated. But if the field has been defined as "null" it's likely to have a null/blank value.

In the first case you will have to check which values are returned by default when date field is defined as not null but it's not populated.

In the second case I would try

If(Len(DateField) = 0, 'No Date', DateField) AS DateField

in the load script.

Hope that helps!

[#3]

Hi - thanks for the reply, I haven't had much chance to look at Qlikview today, but am hoping to do so in the near future to test out what you say - just didn't want you to think I was being ignorant!

Thanks again

Si

[#4]

other things to try to identify the null field

 isnull(DateField)
DateField=''

or instead of setting the value to 'No Date' set it to a default date like '01/01/1753' or '31/12/2999' or anything well outside the normal range of dates so that it appears at the begining or and of any list of dates.