3 Replies Latest reply: Oct 17, 2014 3:05 AM by Massimo Grossi RSS

    date tables

    Suresh Babu

      i have date format like this

       

      9/10/2013

       

      4-10-2014

       

      this two format have in my data

      can i load this data to script or do you what to modify data any one format before loading. 

        • Re: date tables
          Jonathan Dienst

          Hi

           

          You can do it with something like this (change the field name DateField to match your data):

           

          LOAD

               Date(Alt(Date#(DateField, 'D/M/YYYY'), Date#(DateField, 'D-M-YYYY'))) As DateField

           

          HTH

          Jonathan

          • Re: date tables
            Anand Chouhan

            Use Alt function for this see the example for this

             

             

            LOAD Date, Alt( date#( Date , 'DD/MM/YYYY' ),

                                       date#( Date , 'DD-MM-YYYY' ),

                                     ) as  NewDate;

             

            LOAD * Inline

            [

            Date

            9/10/2013

            4-10-2014

            ];

            • Re: date tables
              Massimo Grossi

              see here (pag 8)  for detail

               

              QlikView Date fields

               

               

              TIP 8: FIELDS WITH MIXED DATE FORMATS

              If you have mixed date format in one field or you have data from different sources using

              different formats, you can use the Alt() function to define several possible date formats:

              Alt( Timestamp#(MixDate,'M/D/YYYY h:mm tt'),

              Date#(MixDate,'M/D/YYYY'),

              Date#(MixDate,'D/M/YYYY'),

              Date#(MixDate,'YYYYMMDD'),

              Date#(MixDate,'DD.MM.YYYY'),

              Date#(MixDate'YYYY-MM-DD')

              ) as MixDate

              The order of the interpretation functions also defines the priority when several formats are

              possible for one specific field value, e.g. 8/4/2012, which in the United States means 4th of

              August, but in the United Kingdom means 8th of April.