Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Excel import, date format wrong?

Dear all,

I am trying to import an Excel-file into Sense. Everything seem to work well, except for the date format. When I do the quick import, Sense converts my dates to the numerical value. See included screen dumps. What can I do to make me get the correct date format? I have tried to go via csv-format, and the dates are then correctly displayed, but that gives me the issue of not converting all the package IDs (column A) correctly i.e they are truncated, and are useless for my analysis.

Excel_to_QlikSense_Date_Issue.PNG.png

Sense_Quick_import_from Excel_file_Wrong_date_format.PNG.png

Look at the column Created Date (column D) and you will see that I do not get the real date but the numerical representation. That is of course of no use for me. All date fields get the same conversion (error).

Please advise

brgd

Magnus

1 Solution

Accepted Solutions

Re: Excel import, date format wrong?

If you edit the script, in the left panel should be a tab with the excel name, inside this, the script will be something like:

LOAD PACKAGEID,

     SHIPEMENT...

...

     "CREATED DATE"

...

You need to change this "CREATED DATE" for:

Date("CREATED DATE", 'YYYY-MM-DD') as "CREATED DATE"

4 Replies

Re: Excel import, date format wrong?

Hi Magnus, dates are numbers, you can apply a format, if you use in script Date([CREATED DATE], 'YYYY-MM-DD') it will save the date visible as the Excel format.

Even being visible in that format, internally for QV will still be a number.

Not applicable

Re: Excel import, date format wrong?

Ok, where in my import script should i put that? I am not yet an avid script writer so bear with me

Re: Excel import, date format wrong?

If you edit the script, in the left panel should be a tab with the excel name, inside this, the script will be something like:

LOAD PACKAGEID,

     SHIPEMENT...

...

     "CREATED DATE"

...

You need to change this "CREATED DATE" for:

Date("CREATED DATE", 'YYYY-MM-DD') as "CREATED DATE"

Not applicable

Re: Excel import, date format wrong?

Thank you very much, worked just fine. Also learned a little about how to write the scripts, very helpful