Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Not applicable

Export to Excel Issues on Qlik Sense

As a new user who previously used Qlikview, we are migrating over to Qlik Sense, and I'm finding that in Sense the excel exports are changing the format of certain fields that were not a problem in View, namely:

  • fields that have leading zeros are exported to excel with the zeros scrubbed out (e.g. 00000123 shows as 123)
  • Zip codes or latitude/longitude coordinates are being reformatted in scientific notation (e.g. 12345678901 as 1.23E+10)

I've tried formatting the fields again by wrapping them in the TEXT() function but it still reformats those columns once in excel. In Qlikview this used to work just fine and these columns nicely exported with all the leading zeros or large number of digits. Does anyone know how to prevent this formatting issue in Sense?

5 Replies
reddys310
Honored Contributor II

Re: Export to Excel Issues on Qlik Sense

Hi notbuyingit,

I agrees with you with the first point. I have faced issues like that. But for the second point you have mentioned, If you specify the coordinate as a num(), I believe it would show it as a number. Worth giving a try!

Hope the excel export functionality improves with the upcoming release.

Thanks,

Sangram

Not applicable

Re: Export to Excel Issues on Qlik Sense

Thanks Sangram. I tried formatting with the NUM() function as well, regarding the latitude and longitude coordinates, and they still get reformatted/truncated in the excel export. I even tried surrounding the num function with a text function (e.g. TEXT(NUM(34.360666199999997, '0.000000000000000')) and the export comes out as 34.3606662 instead of the full number.

I can only hope this bug gets fixed soon. As our users are going to be migrated off of Qlikview to Sense, I know they will notice this bug immediately <sigh>

reddys310
Honored Contributor II

Re: Export to Excel Issues on Qlik Sense

Hi,

I am hoping for the same. Hope its fixed with the next release.

Thanks,

Sangram

dennisnet
Valued Contributor III

Re: Export to Excel Issues on Qlik Sense

Maybe this will help.


I had the same problem with format after exporting a NUM() format to Excel.

But I noticed the the MONEY() format was not having this issue.

So here is what I tried:

Instead of:

NUM(value, '#.##0')    or      Num(Sum(value),'#.##0')

I tried :

Money(value, '#.##0')    or      Money(Sum(value),'#.##0')


Maybe a strange work around but it worked for me. Hope it is helpfull.

Dennis.

Not applicable

Re: Export to Excel Issues on Qlik Sense

Thanks Dennis and sorry for the late reply. Was working through some other challenges with Qlik Sense, but I finally got a chance to try your suggestion. Unfortunately no such luck, my values with the preceding zeros don't even show when I attempt to use the Money format as you described .

Thanks again

Community Browser