topic Exporting to Excel if number format is part of expression in QlikView

Exporting to Excel if number format is part of expression

Mon, 30 May 2011 14:05:20 GMT

Hi guys,

I need your help please!

I have developed Financial Statements in QlikView and each expression contains if statements as well as the number formatting for the column.

I use 'Expression Default' on the Number tab.

When I export to Excel, I lose all my number formatting.

Apart from copying to Clipboard, is there perhaps another way that I am able to keep my number formatting?

All my % and rounding is now lost, when exporting, so the statements are very difficult to read.

Please advise.

Thanks.

Regards,

Hesten

Exporting to Excel if number format is part of expression

Mon, 30 May 2011 18:30:16 GMT

Anybody?

Re: Exporting to Excel if number format is part of expression

erichshiino — Mon, 30 May 2011 22:16:02 GMT

Hi,

I'm not sure if it can be done in another way (Actually, for me it exports with the formats)

but you could write your own exporting macro and then include some code to reformat the resulting sheet

In my attachment I included some samples.

Hope it helps,

Erich

Re: Exporting to Excel if number format is part of expression

danielrozental — Tue, 31 May 2011 01:19:43 GMT

As Erich said, I'm afraid it can't be done without a Macro.

Re: Exporting to Excel if number format is part of expression

mike_garcia — Tue, 31 May 2011 04:33:34 GMT

You could use the Text() Function like this:

Text(Num(Sum(Sales), '###,###.##'))

When sending to Excel, it will treat your values as text and therefore will keep the original format.

Since the values will not be numeric, however, you will not be able to "play" with them as you normally would in excel, at least not without some changes after exporting them. Also, the alignment might not be maintained.

Mike.