Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

exporting from qlikview to excel the date format to be mmm-yy

Hi,

When I export data to excel the date format is not kept the same as in the Qlikview model. I have my SET DateFormat='MMM-YY';, but when it exports to excel it exports as "dd-mmm" format.

However, if I export for a few months which is less data it exports fine, but once i export for more months which is more data (more records), it exports as "dd-mmm" format !

Anyway around this to be exported in the same format as the model which is "MMM-YY"??

Regards, Jivesh

11 Replies
stigchel
Not applicable

Re: exporting from qlikview to excel the date format to be mmm-yy

Have a look at this thread, last comment maybe a workaround..

Export to Excel issue

hirishv7
Not applicable

Re: exporting from qlikview to excel the date format to be mmm-yy

Hey,

Try this for your date field,

Date(Date#(DateField),'MMM-YY')


Wrong date format when export to Excel


HTH,

Hirish

“Aspire to Inspire before we Expire!”
Not applicable

Re: exporting from qlikview to excel the date format to be mmm-yy

Hi Hirish

This is my scrip below, where would I place your recommendation scrip?

I have also did screen shots of the Model, the date format in the Model is correct, but when I export the data to excel, csv, it exports in the date format as dd-mmm. This only happens when I export for many records of data. I have read the "Wrong date format when export to Excel" stream no soluton cn be derived from this one so as the one from stigchel.

FOR EACH vSheet IN '[Secondary FY14]','[Secondary FY15]','[Secondary FY16]','[Secondary FY17]','[Primary FY14]','[Primary FY15]','[Primary FY16]','[Primary FY17]'

Primary_Secondary:

LOAD Secondary_Primary,

     Brand,

     [Vitabiotics Brands],

     Product_SKU,

     [SSD Suppliers_Primary Wholesalers],

     [SSD Customer Name_Primary Ship-to],

     MedPages_Code,

     Year,

     MONTH,

     MAT,

     TEAM,

     [Courier_Non COURIER],

     [RX Territory],

     [Self Care Territory],

     Region,

     [RX RSM Region],

     [Self Care RSM Region],

     Value,

     [SSD Adj Value (SEP)],

     Units

FROM

(ooxml, embedded labels, table is $(vSheet));

Qlikview Model with Month.JPGExport data.JPG

hirishv7
Not applicable

Re: exporting from qlikview to excel the date format to be mmm-yy

Hi,

check like this using,

Date(Date#(Month,'MMM-YY'),'MMM-YY') as Month

At Month field in your script.

HTH,

-Hirish

“Aspire to Inspire before we Expire!”
stigchel
Not applicable

Re: exporting from qlikview to excel the date format to be mmm-yy

I'm starting to think this is related to your windows regional settings for date, can you check?

Not applicable

Re: exporting from qlikview to excel the date format to be mmm-yy

Hi Hirish

When I use this scrip, my Months disappear?

Not applicable

Re: exporting from qlikview to excel the date format to be mmm-yy

Hi Piet,

My regional settings are set correctly.

hirishv7
Not applicable

Re: exporting from qlikview to excel the date format to be mmm-yy

Try exporting only month list-box to excel.

“Aspire to Inspire before we Expire!”
jontydkpi
Not applicable

Re: exporting from qlikview to excel the date format to be mmm-yy

You have two options:

  • Export the field as text - this will allow you to control the format, unless Excel recognizes the date format and   converts it to a date, in which case Excel controls the format
  • Export the field as a date - Excel will control the format
Logic will get you from a to b. Imagination will take you everywhere. - A Einstein