When a field contains invalid date, it is not marked with $date tag.

[#1]

Hi,

I import CSV files, which contain dates in YYYY-MM-DD format. Some of fields are nullable, which is presented in the file as 0000-00-00.

When I import files, the fields which contain valid dates, are marked with tags $numeric, $integer, $timestamp, $date
while the fields which contain 0000-00-00, are marked with tags $numeric, $integer only.

I tried many different ways to teach QlikView to threat them as $timestamp, $date, but without success.

Some of my tries:

LOAD Date(Datefield,'YYYY-MM-DD') as Datefield

LOAD Date(Date#(Datefield,'YYYY-MM-DD'),'YYYY-MM-DD') as Datefield

LOAD Date(Date#(if(Datefield='0000-00-00',Null(),Datefield),'YYYY-MM-DD')) as Datefield

LOAD Date(Date#(if(Datefield='0000-00-00','2000-01-01',Datefield),'YYYY-MM-DD')) as Datefield

etc.

The problem is when the field is not marked with $date tag, a lot of functionality in my diagrams doesn't work properly - set analysis, default formatting, where clauses etc.

Georgi

[Miguel_Angel_Baeyens · Accepted solution]

Hi Georgi,

There are no data types in QlikView as such. QlikView can interpret and represent any value as numeric or literal (string). Dates are actually numeric values. When represented using Date() function, values are literals and hence must be properly single quoted and so. Having this function

Date(Today())

the result will be "10/10/2011". So if you want to use that in a set analysis, you must use in single quoted:

Sum({< DATE_FIELD = {'$(=Date(Today())'} >} Sales)

That will work provided values in "DATE_FIELD" are formatted alike the returned byt the Date() function. If you DATE_FIELD stores something like '10/10/2011 18:01' then you need to do some additional formatting, since '01/01/2011' is not equal to '10/10/2011 18:01' nor is the underlying numeric value  (40544 the former, 40544,75069 the latter).

So make sure you use same formats in functions and script when LOADing data into QlikView.

Feel free to post some sample application to check further and see what may be hapenning.

Hope all the above makes sense.

Miguel Angel Baeyens

BI Consultant

Comex Grupo Ibérica

[#3]

Hi Miguel,

Thanks for the answer, but I cannot agree with you - according QlikView Reference Manual:

"QlikView can handle text strings, numbers, dates, times, time stamps and currencies

correctly. They can be sorted, displayed in a number of different formats and they can

be used in calculations. This means e.g. that dates, times and time stamps can be

added to or subtracted from each other."

The problem I have: When the field contains invalid dates, it will be threated as mixed content, and thus we cannot compare it directly to some date value, like >=Today(), we must ALWAYS compare it to Date(Today()), and this is crazy, because now we compare strings, not dates, and the comparison result depends on date format, not on the date alone.

There are also another disadvantages when the dates are not threated as dates, specially in presentation of values.

[erichshiino]

Hi, Georgi

I usually create an auxiliary field to be independent of QV interpretation of date fields ( and to be able to use it without problems on set analysis or other expressions).

Thus, I recommend you create a numeric field for your dates.

The syntax would be:

LOAD num(Datefield) as NumDatefield ,

DateField ...

When using it on set analysis, the syxtax is:

Sum({< NumDatefield_FIELD = {'$(=num(Today())'} >} Sales)

Hope this helps,

Erich

[#5]

I found the cause for the problem:

QlikView threats values between 1980-01-01 and 2080-01-18 as date only, and my data contain dates up to 2099.

Sample:

LOAD *

INLINE [

NullDate,TooLow,MinValidDate,Today,MaxValidDate,TooHigh

0000-00-00,1979-12-31,1980-01-01,2011-10-12,2080-01-18,2080-01-19

];

FieldName     Tags

NullDate          $ascii, $text

TooLow           $numeric, $integer

MinValidDate   $numeric, $integer, $timestamp, $date

Today             $numeric, $integer, $timestamp, $date

MaxValidDate $numeric, $integer, $timestamp, $date

TooHigh          $numeric, $integer

It seems like a bug in QlikView, right?

My workaround was:

Date(Date#(if(DateField<'1980-01-01',Null(),if(DateField>'2080-01-18','2080-01-18',DateField)),'YYYY-MM-DD')) as DatefieldNew

(I don't have dates < 1980-01-01, thus I didn't distinguish between 0000-00-00 and such dates).