3 Replies Latest reply: Feb 4, 2015 8:51 AM by Henric Cronström RSS

    XML timestamp

      Hi,

       

      I'm trying to load an xml (internet) document in Qlik Sense. I have some difficulties parsing timestamp fields. My data looks like "2015-02-03T13:55:00". By default, Qlik recognises this as a string field. I tried modifying the timestamp format with SET TimestampFormat='YYYY-MM-DD hh:mm:ss[.fff]'; also tried 'YYYY-MM-DDThh:mm:ss[.fff]'. Which unfortunately doesn't work

       

      What seems to work is using the replace function to change the T into a whitespace. (e.g. replace("FIELD1", 'T', ' ') as "FIELD1".

       

      Is there a clean solution for this problem? Otherwise I have to apply this workaround for every timestamp field.

       

      Any help is greatly appreciated.

        • Re: XML timestamp
          Marco Wedel

          Hi,

           

          this will load the timestamp field in your default timestamp format defined by the variable TimestampFormat:

           

          LOAD Timestamp(Timestamp#(PurgeChar(Timestampfield,'T'),'YYYY-MM-DDhh:mm:ss')) as Timestampfield
          FROM yoursource;
          

           

           

          hope this helps

           

          regards

           

          Marco

            • Re: XML timestamp

              Hi Marco,

               

              Thanks, yes this probably works. However it is another variation on my forementioned workaround. My question remains, Is it possible to have Qlik Sense correctly recognise timestamp fields when there is a "T" in it? (standard xml timestamp notation). The point is that I'm trying to avoid applying a workaround for every timestamp field I'm using.

                • Re: XML timestamp
                  Henric Cronström

                  Unfortunately it is not possible at this stage. Use Marco's work-around, or

                  Timestamp#(Left(Timestampfield,10) & Mid(Timestampfield,12),'YYYY-MM-DDhh:mm:ss')

                   

                  But we are looking into how we can make the Qlik engine accept modern timestamps with e.g. 'T' or time zone information. Neither is possible to interpret today.

                   

                  HIC