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

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

Wed, 04 Nov 2015 13:42:27 GMT

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?

oknotsen — Wed, 04 Nov 2015 15:09:27 GMT

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

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

Anonymous — Thu, 05 Nov 2015 02:18:16 GMT

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.

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

jagan — Thu, 05 Nov 2015 02:46:38 GMT

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.

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

jonathandienst — Thu, 05 Nov 2015 05:33:14 GMT

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