Qlik Community

New to Qlik Sense

Discussion board where members can get started with Qlik Sense.

Announcements
Save $300 - Register for QlikWorld (formerly Qonnections) by January 31st: Learn More
c_rusu85
New Contributor II

In QSB how to download a sheet as xlsx and the NUMBERS not to start with the ' sign

Hi,

In QSB when I download a sheet in xlsx all the numbers have in front a ' sign (see attached) that makes the numbers to be interpreted in excel as string not as numbers.

In QSB I setup correctly in number formatting the field to be number. In the old version of Qlik Sense Cloud this problem didn't exist, for the same Qlik sheet the same data in the downloaded xlsx file was presented as numbers without the ' sign in front.

Is there any setup I must make in QSB or any way I can correct this in QSB without manually editing the xlsx file each time I download it by using find and replace?

Thanks.

Labels (1)
6 Replies
MVP & Luminary
MVP & Luminary

Re: In QSB how to download a sheet as xlsx and the NUMBERS not to start with the ' sign

You could try to load your field with: purgechar(Field, chr39()) as Field

- Marcus

c_rusu85
New Contributor II

Re: In QSB how to download a sheet as xlsx and the NUMBERS not to start with the ' sign

I tried in the expression with purgechar(ExpressionCode, chr(39)) but when I download the sheet from QSB to a local xlsx file the problem (the single quote apostrophe in front of each number) remains.

In the QSB sheet the data is seen correct, as number!

MVP & Luminary
MVP & Luminary

Re: In QSB how to download a sheet as xlsx and the NUMBERS not to start with the ' sign

You don't want to load the data into Qlik else to export them from there to Excel, right? Then I suggest to set the number-format for this field to numeric and/or to format this column in your export table to numeric or maybe also using num() within the expression.

- Marcus

c_rusu85
New Contributor II

Re: In QSB how to download a sheet as xlsx and the NUMBERS not to start with the ' sign

In Qlik there is no problem, the data is seen as number, the fields are measure and I use expression code in order to process data "live" after is loaded.

The problem is when I download the sheet from Qlik to xlsx. This problem didn't exist in the old Qlik Sense Cloud, when I downloaded the data in xlsx file all fields that was set as number in Qlik in the downloaded xlsx file appeared as number without the single quote apostrophe in front of each number.

The same app (i tried also with a new created up) in the new Qlik Sense Business have this problem.

My opinion is that its not from my code or from my Excel, its from the way that the new QSB generate the xlsx file by automatically inserting a single quote apostrophe in front of each number.

 

PS: I opened a case about this with Qlik support and I got a message from no-reply@qlik.com to send them my app so I`m not sure if they got my app and the case link I received in the email redirects to the Qlik index page 🙂

MVP & Luminary
MVP & Luminary

Re: In QSB how to download a sheet as xlsx and the NUMBERS not to start with the ' sign

I think Qlik don't use any own logic for the Excel exports else it takes any available *.dll's to perform the job. Unfortunately there are several different interpretations from the ooxml-standard: https://en.wikipedia.org/wiki/Office_Open_XML and maybe they replaced one with another or just the release of it might have changed. Even MS used different ones and so is an Excel-file different if it's created with the desktop-client or with the online-client.

If there are no further export-settings within the QSB available which may have an impact on the results you could look within the various xlsx-parts (changing the extension from xlsx to zip and then opening it within the windows explorer and you will find various xml-files). Especially if you compares it with a "normal" xlsx you should find differences and with them maybe a hint how to bypass the issue. Of course it will need some time and there is no guarantee that you get a working solution ...

Further a search within google to a similar behaviour by other tools may give some insights.

- Marcus

jsteyn
New Contributor III

Re: In QSB how to download a sheet as xlsx and the NUMBERS not to start with the ' sign

This used to work in the old QSCB platform and for some time in its infancy of the new QSB platform.

Myself and numerous people have raised this on multiple threads on this forum, would be nice if we can get an update on this from Qlik here?