4 Replies Latest reply: Oct 13, 2011 3:52 AM by Georgi Varbanov RSS

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

      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

        • Re: When a field contains invalid date, it is not marked with $date tag.
          Miguel Angel Baeyens de Arce

          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

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

              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.

               

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

                  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

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

                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).