topic Importing Dates from Excel into QV in QlikView

Importing Dates from Excel into QV

Mon, 25 Apr 2011 19:51:53 GMT

I am having trouble getting the date from excel into qlikview. There a few threads already, but im having trouble following them. So i have a date field in excel that is formatted the following way:

=DATE(2011, 3, 1)

that equals 3/1/2011, which is different from what i am getting in QV.

how i imported my excel file was by opening the "edit script" screen. then at the bottom i clicked the button called "table files" and added my excel file to the script. that is how i got this code:

LOAD WID,

Date,

Description,

Who,

Number,

Importing Dates from Excel into QV

IAMDV — Mon, 25 Apr 2011 20:06:48 GMT

Hi Zachmurray,

QV is generally very good at interpreting the dates, because it stores the dates in to integers/decimal points. I mean in your case it should store the date as 40,603 assuming that there is no time and if there is time then it will store 40,603.xxxxxx something like this...

And if QV don't understand the date then it converts it to string. So I would recommend you to convert your Date Field in to Date Format. You can use Date function in the load script.. something like this...

Date(Date, 'DD/MM/YYYY')

I hope this helps!

Cheers.

Importing Dates from Excel into QV

Mon, 25 Apr 2011 21:13:00 GMT

LOAD WID,

Date(Date,'DD/MM/YYYY),

Description,

Who,

Number,

Importing Dates from Excel into QV

erichshiino — Mon, 25 Apr 2011 21:18:52 GMT

If you want to interpret a string as a date, the syntax is : Date#(FIELD,'DD/MM/YYYY') as Date

In your case, Date#(Date,'DD/MM/YYYY') as Date

What kind of result did you get with your original script?

Importing Dates from Excel into QV

Mon, 25 Apr 2011 23:37:48 GMT

I actually got it to work by using Date(Date,'DD/MM/YYYY') as Date without the "#".

Thanks for the direction

Importing Dates from Excel into QV

IAMDV — Tue, 26 Apr 2011 09:24:56 GMT

Hi Zachmurray,

I am glad to know that you got it working. Please can you mark the post answered? This will be useful reference for other community users...

Cheers!