Export to Excel Issues on Qlik Sense

[#1]

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?

[reddy-s]

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

[#3]

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>

[reddy-s]

Hi,

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

Thanks,

Sangram

[Anonymous]

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.

[#6]

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