Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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!
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
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.