topic Re: How to keep the multi-line headers when exporting to excel? in QlikView

How to keep the multi-line headers when exporting to excel?

stevelord — Wed, 01 Oct 2014 15:35:26 GMT

Hi, I have some reports where the headers have 2-4 words, but the data below is mostly single digit stuff, so I set the headers to be 2-lines and let the columns be a little narrow. When I export to excel, the headers all go back to one line and often last part of the header is hidden under the header to the right.

How do I get excel to keep the headers wrapped?

Presently, the headers are 'Logged' 'Logged, Goal Met', 'Logged, Goal Not Met', '0 Logged' and when the headers unwrap, they just look like Logged Logged Logged Logged. Confusing to leave them like that, and annoying to have to format/wrap text row 1 on every export. Interim workaround will be me rearranging or shortening labels but generally wondering if I can get the formatting to stick on export.

Other slightly less annoying one, is how do I get the commas to stick in the numbers when exporting to excel? I can understand not keeping commas when exporting to csv, but should be good to keep them going to excel right? We do have some seven to twelve digit numbers in some totals here and there and people sometimes ask for commas back in their numbers.

Maybe least annoying one is when I have numbers left aligned in qlikview to keep them under their headers, and excel right-aligns them. I'd like it to cut that out too and just let all my formatting stay like it is in the qlikview... csv can strip it down for necessity, but no need for excel to do that...

Re: How to keep the multi-line headers when exporting to excel?

vgutkovsky — Wed, 01 Oct 2014 16:16:41 GMT

Regarding the commas question, I believe as long as your expression is formatted under the Number tab (as opposed to just using function num()) then number formatting sticks when exporting to Excel.

Multi-line headers are trickier. The only real way to do that is through a macro, where you can define all sorts of Excel properties. There are downsides to that of course, such as incompatibility with AJAX.

Vlad

Re: How to keep the multi-line headers when exporting to excel?

stevelord — Wed, 01 Oct 2014 16:34:15 GMT

Stinks that macros are the correct answer- QVMC doesn't let you schedule reloads for things with macros (or maybe it just doesn't run the macro part, I forget), but sounds about right to me given the lack of any other option I could find in my digging.

Thanks too for the numbers bit too, I'll check into that the next time I'm on that dashboard.

Re: How to keep the multi-line headers when exporting to excel?

vgutkovsky — Wed, 01 Oct 2014 16:38:11 GMT

You're right about that--that is an inherent limitation of Publisher. If you set the macro to trigger OnPostReload and then reload via batch ("qv.exe /r myqvw.qvw") then it should work, I think.

Vlad

Re: How to keep the multi-line headers when exporting to excel?

stevelord — Wed, 01 Oct 2014 18:16:56 GMT

FYI, number format 'Number' shows commas in qlikview but drops commas when exporting to excel.

number format 'Integer' shows commas in qlikview and keeps commas when exporting to excel.

(Default other settings, and I have no num() functions in the related expressions.) Tested with different settings side-by-side expressions and one lost the comma while the other kept the comma.

Re: How to keep the multi-line headers when exporting to excel?

vgutkovsky — Wed, 01 Oct 2014 18:24:15 GMT

That's true, sorry I thought you understood what I meant. I just meant the "Number" tab, not the "Number" format type.

Vlad