topic Exporting to Excel Query in QlikView

Exporting to Excel Query

hopkinsc — Thu, 26 Apr 2012 09:23:42 GMT

Hi,

Can anyone tell me if there is a way of exporting to excel in the correct format?

Everytime i export to excel, there are some columns that i have to manually convert to a number format.

The columns with green triangles i have to convert to a number format.

thanks

Exporting to Excel Query

Jason_Michaelides — Thu, 26 Apr 2012 09:29:37 GMT

I think that if you add these dimensions to your chart as calculated dimensions and wrap them in Num() then the export will work properly.

Exporting to Excel Query

hopkinsc — Thu, 26 Apr 2012 09:31:47 GMT

Hi, that is the first thing i tried but didn't make a difference...

Exporting to Excel Query

Anonymous — Thu, 26 Apr 2012 09:34:12 GMT

Having the same problem overhere with different types of data columns. When having a money field, this turns out to be a textfield in Excel no matter what I do.

It seems that QV exporting to Excel doesn't look to the 'Properties - Number' set for the table.

Exporting to Excel Query

Jason_Michaelides — Thu, 26 Apr 2012 09:36:51 GMT

Try making them Dimension*1 in your chart? Anything to get Excel thinking it's a number!

Exporting to Excel Query

hopkinsc — Thu, 26 Apr 2012 09:40:24 GMT

Do you mean... =StoreNumber*1?

That didn't work either..

Exporting to Excel Query

Michiel_QV_Fan — Thu, 26 Apr 2012 09:41:40 GMT

1 Step back,

What is your purpose of exporting data to Excel from Qlikview?

Exporting to Excel Query

hopkinsc — Thu, 26 Apr 2012 09:43:46 GMT

We export to excel for the field staff who don't have a Qlikview License..

Exporting to Excel Query

Anonymous — Thu, 26 Apr 2012 09:46:10 GMT

You could also make use of the reporting functionality -- print the report as PDF for the staff

This way the data is exactly what QlikView showed and no changes are made (accidentally or on purpose) to the numbers.

Exporting to Excel Query

hopkinsc — Thu, 26 Apr 2012 09:49:32 GMT

Hi Anita,

Yes i have looked at that, but we export data for Area Managers with info for all of their clients. We export to Excel and put filters on it so it is easier for the Area Managers to view specific info. Obviously we can not do that with a PDF.

We can manage the way it is (With the number format errors), at the moment i manually change the formats, it's just a pain doing it every week for 25+ exports.

Thanks

Exporting to Excel Query

Anonymous — Thu, 26 Apr 2012 09:52:08 GMT

Maybe you should consider Licences for those people .

You use QlikView (great product) and then export it (and manipulate since the export is not numeric correct), create filtering possibilities . . . it's a lot of work and not fun to do.

I say . . . Document CAL

Exporting to Excel Query

hopkinsc — Thu, 26 Apr 2012 10:18:32 GMT

I agree that licenses should be provided. Although our field staff as we have over 100 field staff licenses would be very expensive

Exporting to Excel Query

m_woolf — Thu, 26 Apr 2012 11:04:09 GMT

Use a macro to send the data to Excel and do the formatting in the same macro.

Exporting to Excel Query

Anonymous — Thu, 26 Apr 2012 11:07:20 GMT

Using a macro is a possible solution I guesse . . . especially if you have to do this stuff for over 25 times a week!!
If licenses is not a solution and you really wish for a workable solution for yourself . . . use a macro.

We chosen not to do so, because the effort we had to put in this was to much.

Also we had the problem we weren't allowed to install excel functionality on our server 😉

Exporting to Excel Query

Thu, 26 Apr 2012 11:42:42 GMT

You many need to specify exactly how you want to export it:

For your Delivery Note No. field:

num([Delivery Note No.],'#,##0.0')

or for Dates use:

date(YourDateField,'DD/MM/YYYY')

Exporting to Excel Query

Anonymous — Thu, 26 Apr 2012 11:56:49 GMT

using this in the expresion doesn't work . ..

Exporting to Excel Query

Thu, 26 Apr 2012 12:00:30 GMT

It would need to be used in the dimensions...

Exporting to Excel Query

Anonymous — Thu, 26 Apr 2012 12:01:57 GMT

The dimensions is not really my issue . . . it is the expresions which give the problem

Re: Exporting to Excel Query

Joaquin_Lazaro — Thu, 20 Sep 2012 14:30:07 GMT

Hi:

It was a QV bug, in the next version it will be solved ... read this pdf

Re: Exporting to Excel Query

Joaquin_Lazaro — Wed, 07 Nov 2012 10:50:27 GMT

Hi:

I've found the way to export numeric fields as numeric cells ... but unformatted.

After instal QV 11 SR2 you must following the instructions on this article https://eu1.salesforce.com/articles/Basic/How-to-export-data-to-excel-as-number-in-version-11?popup=true# at the customer portal.