topic Re: Help with date in QlikView

Help with date

qvqfqlik — Tue, 28 Mar 2017 17:31:55 GMT

I need to read date values from excel file which is in format of Jan 31 2017 7:00:41:000AM

I am unable to change the date format in excel, but how to read and change the date format in Qlikview ?

Please suggest all possible ways of reading and changing the format of this in Qlikview

Read just the date . timestamp is not necessary.

Re: Help with date

sunny_talwar — Tue, 28 Mar 2017 17:39:22 GMT

May be this

Date(Floor(TimeStamp#(DateField, 'MMM DD YYYY h:mm:ss:fffTT'))) as Date

Re: Help with date

Kushal_Chawda — Tue, 28 Mar 2017 17:44:16 GMT

After loading the data from excel file in qlikview, if your Date field is in timestamp format, then just use below

date(floor(DateField)) as Date

Re: Help with date

qvqfqlik — Tue, 28 Mar 2017 18:18:42 GMT

I tried both , but I couldn't values into Date1, Date2 fields

Dates:

LOAD
Date as OriginalDateformat,
Date(Floor(TimeStamp#(Date, 'MMM DD YYYY h:mm:ss:fffTT'))) as Date1,
Date(Floor(Date, 'MMM DD YYYY')) as Date2
FROM

Re: Help with date

sunny_talwar — Tue, 28 Mar 2017 18:21:44 GMT

Second one would be this

Date(Floor(Date), 'MMM DD YYYY') as Date2

Re: Help with date

qvqfqlik — Tue, 28 Mar 2017 18:30:42 GMT

Sunny, it still has no data in those fields

Re: Help with date

qvqfqlik — Tue, 28 Mar 2017 18:35:33 GMT

the format in excel is not a date format. I tried this as well, abut still not values in this field

Date(Left([Date], 3) & Mid([Date], 4, 2) & Right([Date], 4)) as Date3

Re: Help with date

maxgro — Tue, 28 Mar 2017 18:38:49 GMT

please post the excel with few rows

Re: Help with date

sunny_talwar — Tue, 28 Mar 2017 18:41:25 GMT

I agree with Massimo... would you be able to post a sample file to test this out?

Re: Help with date

qvqfqlik — Tue, 28 Mar 2017 18:46:56 GMT

I attached excel file to the initial post

Re: Help with date

sunny_talwar — Tue, 28 Mar 2017 18:49:44 GMT

My first response worked (with two spaces between Year and Time....

Date(Floor(TimeStamp#(DateField, 'MMM DD YYYY h:mm:ss:fffTT'))) as Date

Re: Help with date

qvqfqlik — Tue, 28 Mar 2017 19:01:45 GMT

Thanks Sunny! But for few dates it seems not converting.

Re: Help with date

sunny_talwar — Tue, 28 Mar 2017 19:08:21 GMT

Try this:

Table:

LOAD Date as Old_Date,

Date(Floor(TimeStamp#(Replace(Date, ' ', ' '), 'MMM D YYYY h:mm:ss:fffTT'))) as Date

FROM

[..\..\Downloads\Dates.xlsx]

(ooxml, embedded labels, table is Sheet1);

Re: Help with date

qvqfqlik — Tue, 28 Mar 2017 19:11:51 GMT

Thank you very much Sunny. It worked awesome