2 Replies Latest reply: Jan 16, 2018 1:39 PM by Mike Grattan RSS

    Convert string to date

    Mike Grattan

      I have a field called observation_time with string data like this:

       

      Last Updated on Jan 16 2018, 8:45 am PST

       

      I am trying to format it as a standard timestamp.  So far, I have used the following expression:

       

      =Date#(textBetween(observation_time,'Last Updated on ', 'P'))

       

      This finds the date and time information between the beginning and end of the string, so it now looks like this:

      Jan 16 2017, 8:45 am

       

      Although this is a step in the right direction, my next goal is to use the Max() function to get the last record of the data set.  The Max function won't work on this as it still thinks it's a string, and not a date.  I've tried wrapping it in the Date function but that results in an invalid dimension. (I'm testing functions in a table chart in Qlik Sense, but I will be using the script editor to load the data once I figure out how to convert the field).

       

      Any ideas on how to convert this string to a valid date format so I can then use the Max function on the field?

       

      Thanks.