1 Reply Latest reply: Apr 23, 2015 4:02 PM by Jonathan Poole RSS

    Reformat data

      Hi, I'm trying to find a way to re-format the data from a field that supposed to hold date value.

      The raw data looks like:

      2015-04-22T23:12:10.000+0300

      Now when I try to create a line graph with these values the graph shows me a straight line, if I use the "purgechar" function I can only remove specific characters:

      Purgechar(date, 'T23')

      Is there a way to re-format the date so I would be able to create a line graph?

       

      Thanks!

        • Re: Reformat data
          Jonathan Poole

          Try changing the date field in the load script into more usable date buckets.

           

          IE:

           

          CHANGE:

          load

               Date

               ...

          from <>

           

          TO:

           

          Load

               *,

               Date(Timestamp) as Date,

               Year(Timestamp) as Year,

               Month(Timestamp) as Month,

               hour(Timestamp) as Hour;

          Load

               timestamp#( left(Date,10) & ' ' & mid(Date,12,12) ,'YYYY-MM-DD hh:mm:ss.fff')  as Timestamp

               ...

          from <>