Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Save the Date: QlikWorld Online, June 24-25, 2020. Free global virtual event for data integration and data analytic gurus. Register Today
Highlighted
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
Highlighted
Honored Contributor III

Re: How to reformat a text string to a date in the data load editor?

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!
New Contributor III

Re: How to reformat a text string to a date in the data load editor?

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.

Highlighted
MVP & Luminary
MVP & Luminary

Re: How to reformat a text string to a date in the data load editor?

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.

Highlighted
MVP
MVP

Re: How to reformat a text string to a date in the data load editor?

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