Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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);
May be this
Date(Floor(TimeStamp#(DateField, 'MMM DD YYYY h:mm:ss:fffTT'))) as Date
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
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
Second one would be this
Date(Floor(Date), 'MMM DD YYYY') as Date2
Sunny, it still has no data in those fields
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
please post the excel with few rows
I agree with Massimo... would you be able to post a sample file to test this out?
I attached excel file to the initial post