5 Replies Latest reply: Apr 18, 2016 4:50 PM by Uzo Aneke RSS

    Export to Excel Issues on Qlik Sense

    Uzo Aneke

      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?

        • Re: Export to Excel Issues on Qlik Sense
          Sangram Reddy

          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

            • Re: Export to Excel Issues on Qlik Sense
              Uzo Aneke

              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>

            • Re: Export to Excel Issues on Qlik Sense
              Dennis Hoogenboom

              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.