How to reformat a text string to a date in the data load editor?
I have read through the script and syntax guide for Qlik Sense but have not found the answer that I am looking for. I have a column of data that has the date and time stored as a text string. An example of the format is: "20151001 134123" which is "YYYMMDD HHMMSS" in 24 hour time format. What I would like to do is split this column into the following: Year, Month, Date (MM/DD/YYYY), and Time (hh:mm:ss). I have been able to split everything with no issue except for the time. When the data loads, it displays the time as 00:00:00. Is there a way to tell qlik sense to make the right 6 characters into 24 hour time format, or do I have to add the ":" in before telling the script to convert? I am having the same issue with the date. It displays MMDDYYYY but I would prefer that the "/" are displayed between. Any suggestions? My current code is displayed below:
Re: How to reformat a text string to a date in the data load editor?
Load * Inline [
Date (Date# (Left (RawDate,8),'YYYYMMDD'),'MM/DD/YYYY') as ExtractedDate,
Mid (RawDate,10,2) as Hours,
Mid (RawDate,12,2) as Minutes,
Mid (RawDate,14,2) as Seconds,
Date (Date# (Mid(RawDate,10,6),'hhmmss'),'hh:mm ss') as ExtractedTime
Date# converts a number into a Qlikview date (i.e., number of days since 1/1/1900).
Date is used to specify the displayed format of the value. It does not change the underlying value.
Your use of the "time" function is returning all zeros because the string hasn't yet been converted to a QV date/time. You need to perform the conversion first, before you specify how it's to be displayed.