Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

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:

4 Replies
oknotsen
Master III
Master III

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);

May you live in interesting times!
Anonymous
Not applicable
Author

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.

jagan
Luminary Alumni
Luminary Alumni

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.

jonathandienst
Partner - Champion III
Partner - Champion III

>>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;

Logic will get you from a to b. Imagination will take you everywhere. - A Einstein