topic Re: Date/Time format issue in QlikView

Date/Time format issue

arpitkharkia — Tue, 11 Apr 2017 08:39:27 GMT

Hi all,

I have a timestamp field which consists date of the following two formats:

MM-DD-YYYY HH:MM
MM/DD/YYYY HH:MM:SS TT

I want to extract date and time separately in the following formats:

MM-DD-YYYY
HH:MM:SS

Thanks in advance!

Re: Date/Time format issue

sunny_talwar — Tue, 11 Apr 2017 08:49:00 GMT

May be like this

Date(Floor(Alt(

Num(TimeStamp#(TimeStampField, 'MM-DD-YYYY hh:mm')),

Num(TimeStamp#(TimeStampField, 'MM-DD-YYYY hh:mm:ss TT'))

)), 'MM-DD-YYYY') as Date,

Time(Frac(Alt(

Num(TimeStamp#(TimeStampField, 'MM-DD-YYYY hh:mm')),

Num(TimeStamp#(TimeStampField, 'MM-DD-YYYY hh:mm:ss TT'))

)), 'hh:mm:ss') as Time

Re: Date/Time format issue

Miguel_Angel_Baeyens — Tue, 11 Apr 2017 08:53:52 GMT

To read the value with that specific mask provided it is not recognized by Qlik, you can use Date#():

LOAD
...
// Note the minutes are lowercase "mm" because uppercase "MM" are intended for months
Date(Date#(DateField1, 'MM-DD-YYYY hh:mm')) AS DateField1,
Date(Date#(DateField2, 'MM/DD/YYYY HH:mm:ss TT')) AS DateField2,
...

To display the values you can also use the Date() function with different masks, or create two different fields in the data model, using the same logic as above.

Re: Date/Time format issue

arpitkharkia — Tue, 11 Apr 2017 09:01:09 GMT

Hi Sunny,

Its not working for me. I have attached the file. If possible please check!

Re: Date/Time format issue

sunny_talwar — Tue, 11 Apr 2017 09:25:44 GMT

For the sample attached, this worked

Table:

LOAD Date(Floor(Call_Date), 'MM-DD-YYYY') as Date,

Time(Frac(Call_Date), 'hh:mm:ss') as Time

FROM

[..\..\..\Downloads\CallDate.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Date/Time format issue

arpitkharkia — Tue, 11 Apr 2017 09:34:44 GMT

if you observe that dates after 11/2/2017 are not being considered. How to handle that?

Re: Date/Time format issue

Anil_Babu_Samineni — Tue, 11 Apr 2017 10:24:10 GMT

Seems works,

Qlikview

Excel

Re: Date/Time format issue

arpitkharkia — Tue, 11 Apr 2017 10:36:53 GMT

If I am right all the dates are not of FEB. The file I have attached is only for Feb. How to handle that?

Re: Date/Time format issue

tresB — Tue, 11 Apr 2017 10:46:22 GMT

The below worked:

Date(Floor(Alt(

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm'),

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm:ss TT')

)), 'MM-DD-YYYY') as Date,

Edit: removed unnecessary num()

Re: Date/Time format issue

sunny_talwar — Tue, 11 Apr 2017 10:47:02 GMT

I am not sure what you mean?

Re: Date/Time format issue

arpitkharkia — Tue, 11 Apr 2017 10:53:57 GMT

if you look at the circled values , they are the 2nd day of different months. The file I have posted only contains Feb month data.

Re: Date/Time format issue

arpitkharkia — Tue, 11 Apr 2017 10:54:52 GMT

Thanks its looking good. But its not showing dates from 1st to 12th Feb?

Re: Date/Time format issue

sunny_talwar — Tue, 11 Apr 2017 11:01:55 GMT

Seems like a problem within your Excel file itself.... The Call_Date shows it as April for the first entry... check it out

Re: Date/Time format issue

arpitkharkia — Tue, 11 Apr 2017 11:27:07 GMT

Oh yeah. May be the file im getting is in that format only. Is it any way that I can extract dates from there considering that data is only for the month of Feb? Help appreciated!

Re: Date/Time format issue

tresB — Tue, 11 Apr 2017 11:31:57 GMT

That is because your those dates are being read as numbers and not like you see in the excel. If you try with the below script, you will get all of them but with different dates that don't expect. Here you won't be able to handle that in qv rather you have to change in the source, i.e. excel per se.

Date((Alt(

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm'),

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm:ss TT'), Call_Date // Call_Date when numeric

)), 'MMM-DD-YYYY') as Date

Re: Date/Time format issue

tresB — Tue, 11 Apr 2017 11:37:31 GMT

May be like:

LOAD

Date(Floor(Alt(

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm'),

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm:ss TT'),

Call_Date

)), 'MMM-DD-YYYY') as Date, Call_Date

FROM

Where

Month(Alt(

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm'),

TimeStamp#(trim(Call_Date), 'MM/DD/YYYY hh:mm:ss TT'),

Call_Date

)) ='Feb';

Re: Date/Time format issue

sunny_talwar — Tue, 11 Apr 2017 11:39:20 GMT

Here you go... try this

SET DateFormat='M/D/YYYY';

SET TimestampFormat='M/D/YYYY h:mm:ss TT';

Table:

LOAD Call_Date,

Date(Alt(Date#(Date(Num#(Text(Call_Date))), 'D/M/YYYY'), Date(Floor(Call_Date))), 'MM-DD-YYYY') as Date,

Time(Frac(Call_Date), 'hh:mm:ss') as Time

FROM

[CallDate.xlsx]

(ooxml, embedded labels, table is Sheet1);