Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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:
I suggest using the date#() function (not to be mixed up with the date() function; the # is part of the function name).
Using that function you can tell Qlik how the interpretation of the text should be.
In your case I think you should do something like this:
Load
year(SaveDate) as Year,
month(SaveDate) as Month,
etc, etc, etc, // create whatever date or time related fields you want out of SaveDate
* // this loads all the (other) fields from the previous load
;
Load
date#(SaveDate, 'YYYMMDD hhmmss') as SaveDate,
the other fields
FROM [lib://WK_RL_Data/926705-100_201510*.CSV]
(txt, codepage is 1252, embedded labels, delimiter is ';', msq);
Try:
OriginalData:
Load * Inline [
RawDate
20151001 134123
];
FormattedData:
Load
*,
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
Resident OriginalData;
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.
HI,
You can convert datetime format using TimeStamp#() like below
Data:
LOAD
*,
Year(Time_Formatted) AS Year,
Month(Time_Formatted) AS Month,
Date(Floor(Time_Formatted)) AS Date;
LOAD
*,
TimeStamp#('20151001 134123', 'YYYMMDD hhmmss') AS Time_Formatted
FROM DataSource;
Hope this helps you.
Regards,
Jagan.
>>What I would like to do is split this column into the following: Year, Month, Date (MM/DD/YYYY), and Time (hh:mm:ss).
To add to Jagan's script:
Data:
LOAD
*,
Time(Frac(Time_Formatted)) As Time,
Year(Time_Formatted) AS Year,
Month(Time_Formatted) AS Month,
Date(Floor(Time_Formatted)) AS Date;
LOAD
*,
TimeStamp#('20151001 134123', 'YYYMMDD hhmmss') AS Time_Formatted
FROM DataSource;
If you want the time or date in a format different to default time on your system, then add the format to the Date() and Time() functions. For example:
Time(Frac(Time_Formatted), 'hh:mm:ss') As Time,
Date(Floor(Time_Formatted), 'YYYY/MM/DD') AS Date;