topic EXCEL EXPORT ISSUE in QlikView

EXCEL EXPORT ISSUE

madhukar_putta — Sat, 31 Aug 2013 09:24:00 GMT

Hello all,

I am new for QLIKVIEW tech. I have an issue in my application. While I took option "SEND TO EXCEL" in any straight table or pivot table chart, the date format is changing in EXCEL sheet when data is in large content (Huge data).

Please find the excel for the screenshots. For small data there is no issue. Where as for large data this problem occurs. In the screen shot i marked it in RED colour.

In QVD'S date format was "DD-MM-YYYY" but in excel for dates below 12th was showing like "DD/MM/YYYY" as shown in image. Please provide some solution for this. Thanks in advance.

Re: EXCEL EXPORT ISSUE

marcus_sommer — Sun, 01 Sep 2013 08:21:31 GMT

Your excel-export returns for the dates string-values. Some of these values will from excel automatically converted into numeric values - see here the different horizontal alignment by string and numeric values. To solve this issue set in the object properties from the table under number tab the format explicitly on date.

- Marcus

Re: EXCEL EXPORT ISSUE

madhukar_putta — Mon, 02 Sep 2013 15:47:53 GMT

Hello Marcus,

Thanks for the reply. I checked object properties, under number tab only expressions fields will available. But in my case all are dimensions in the chart. So please guide. Thanks.

Re: EXCEL EXPORT ISSUE

madhukar_putta — Mon, 02 Sep 2013 15:48:27 GMT

Hello Marcus,

Thanks for the reply. I checked object properties, under number tab only expressions fields will available. But in my case all are dimensions in the chart. So please guide. Thanks.

Re: EXCEL EXPORT ISSUE

marcus_sommer — Tue, 03 Sep 2013 06:17:59 GMT

You could also each dimension define - go to document properties and then to the tab numbers.

- Marcus

Re: EXCEL EXPORT ISSUE

jonathandienst — Tue, 03 Sep 2013 06:36:21 GMT

I think Excel is expecting dates in MDY order, rather than the DMY that Qlikview is exporting. That's why Excel can convert dates before the 12th, as these are valid for MDY dates. Dates after the 12th are not valid. Excel is displaying MM/DD/YYYY dates, not DD/MM/YYYY dates.

Excel determines the date format from the regional settings on your PC. You can modify the model to export in MDY format, or change your regional settings to match the QV document.

Hope that helps

Jonathan

Re: EXCEL EXPORT ISSUE

ashwanin — Tue, 03 Sep 2013 07:09:49 GMT

Hi Madhukar,

Open the QlikView model ,click on Edit Script and Change the Date and Time stamp Format.

Reload the model and send the data to excel.

rgds

Ashwani

Re: EXCEL EXPORT ISSUE

Tue, 03 Sep 2013 11:02:42 GMT

Hi Madhukar,

Have you tried to go to the Config menu, inside Document Properties option go to "Number" tab and select into the left column the date fields (PO_DATE, DDD_adj, etc.) that you are using and define the date format?

Re: EXCEL EXPORT ISSUE

madhukar_putta — Tue, 03 Sep 2013 14:12:57 GMT

Hello Marcus,

I tried the option but still issue not solved. Is there any other way ?

Re: EXCEL EXPORT ISSUE

madhukar_putta — Tue, 03 Sep 2013 14:15:23 GMT

Hello Merino,

Thanks for the reply.

I tried that option too but still not solved.

Re: Re: EXCEL EXPORT ISSUE

marcus_sommer — Tue, 03 Sep 2013 14:27:08 GMT

For me it worked - you have here changed the properties?

- Marcus

Re: Re: EXCEL EXPORT ISSUE

madhukar_putta — Tue, 03 Sep 2013 14:38:39 GMT

I modified there only but no impact. My actual issue is .....For small data like 65000 records there is no issue even for the dates after 12th eg: 20-09-2012. Its coming perfectly . But for large data records like 2lakh records, this issue raises...automatically its converting format instead of ' - ' its taking ' / ' . when ever my user downloads (send to excel option) huge data then only this issue occurs. Please guide me. Thanks in advance.

Re: Re: EXCEL EXPORT ISSUE

marcus_sommer — Tue, 03 Sep 2013 14:49:40 GMT

If it only happens with large amounts of data it could be that then QV had problems with the meta-data (string, numeric and so on) from the data. Also a reason could be various user- and system settings - is it the same for all users and with all clients?

- Marcus

Re: EXCEL EXPORT ISSUE

quwok — Tue, 03 Sep 2013 14:59:41 GMT

Hi Madhukar

Are you sure QlikView detected all entries as "dates", and not some as dates and others as string?</>

In your load script, try converting the column using the date#() function.

Re: Re: EXCEL EXPORT ISSUE

madhukar_putta — Tue, 03 Sep 2013 15:38:36 GMT

My application is using by one client and we have 15 users as of now. Issue is in procurement module. That module is using by 7 users. All are getting same problem.......

Re: EXCEL EXPORT ISSUE

madhukar_putta — Tue, 03 Sep 2013 15:39:45 GMT

Hello Kwok,

All are dates only. This issue is with large data only.

Re: EXCEL EXPORT ISSUE

marcus_sommer — Tue, 03 Sep 2013 15:51:52 GMT

I believe on this way it won't work - if your user not calculate with these dates you could load and export these dates explicitly as strings. Perhaps another possibilities could be to use a macro-routine (there are several different) - see for examples here: http://community.qlik.com/search.jspa?q=macro+excel+export.

- Marcus

Re: EXCEL EXPORT ISSUE

Tue, 29 Apr 2014 07:43:54 GMT

This is the correct answer.

Re: EXCEL EXPORT ISSUE

jagan — Tue, 29 Apr 2014 08:29:51 GMT

Hi Madhu,

Can you please attach the sample data with both the date formats. I think there are two date formats in you dimension, that is causing the issue.

Regards,

jagan.