Excel Export - Numbers as Text

[Giovane]

Hi guys...

I'm using QlikView Personal Edition version 12.10.20500.0 and when I export the content from a pivot table to the MS_Excel, Numbers as have been exported as Text. What can I do to overcome this issue?

[tincholiver · Accepted solution]

If you go to the properties of the graphic in the tab number you can format the cell and export it as a number

[Giovane · Accepted solution]

Thank you Tincholiver...

Your suggestion really worked. The strange situation is, before formatting the field, it already has a Number format. But after the forced formatting it was exported as Number. 

Thank you very much !!!

[Bill_Britt · Accepted solution]

Hi,

Numerical export to Excel can still be forced by an an Easter Egg setting in QlikView. The setting is called ExcelExportMixedAsText and defines if mixed format data should be exported as text or numerical value. 

The default value for ExcelExportMixedAsText is 1 and means that export of mixed data will be done as text. If the value is set to 0, mixed fields will be exported as numerical values.

Keep in mind that when ExcelExportMixedAsText is 0 the numerical values will be exported based on the stored or calculated result, and not on the currently presented format in QlikView. This can be see in the example above where the 5% value is exported with the underlying numerical value of 0.05  and the two decimal value 800000.80 is exported with the underlying numerical value of 800000.8.

Enable Easter Egg feature in QlikView Desktop client

1. Open QlikView Desktop Client
2. Open the QVW file that requires the change
3. Go to Help > About QlikView…
4. Right click on the QlikView logo in the lower left corner of the about dialog
5. Highlight ExcelExportMixedAsText in the settings list
6. Enter the desired value to export mixed values as text (1) or numbers (0)
7. Click Set to store the value
8. Click Save Next Script on Load
9. Click Close to close the settings dialog
10. The limit has now been changed in the QlikView client, and will affect all QlikView applications opened in the client

Enable Easter Egg on QlikView Server

1. Change the QVW level setting as described in above section
2. Save or publish the QVW to the QlikView Server Documents folder
3. Open the QlikView Server’s settings.ini file in a text editor. Default file path; C:\ProgramData\QlikTech\QlikViewServer\Settings.ini
4. Add ExcelExportMixedAsText =nnn in the [Settings 7] section of the settings file. Note that nnn should be replaced with the same value as specified in step 6 in above section
5. Save the settings file and close text editor
6. Restart QlikView Server, to trigger loading of the altered settings 
7. Access the QVW from QlikView web client to see that the behavior has been changed for all QlikView applications accessed through the server

Bill

[Giovane]

Hi, Bill...

Your suggestion was really cool. Even more cause it showed me a new place where settings can be applied on QlikView.

Thanks a lot.

Giovane

[cotiso_hanganu]

Is there a way to do the same in Qlik Sense , with or without Easter egg !?

[Chip_Matejowsky]

Hi @cotiso_hanganu,

As this thread is for QlikView and over 4 years old, suggest you start a new thread regarding your question on the Qlik Sense forums.

Best Regards

[cotiso_hanganu]

The issue is actually in the support log for a month now. Once solved, you can make an article

[daveatkins]

The easter egg sounds like it would create more problems than it would solve since it sounds like it applies to all dashboards on the server. Too risky to try that!

The fundamental problem here is that when users send a table to excel, they get a mess of green triangle tick marks and data that is text and cannot be used in calculations. So they have to convert to text manually.

The object -> properties -> Number -> Expression

Number Format Settings 

option is not feasible when the data in the table has different formats for each column (this is common when reporting financial data along with numerical data)

But there IS a sort of solution. Users can be told to export instead of send to Excel. Right click on the table, then choose Export instead of just "send to Excel." This produces a .csv file which appears to work correctly!

In our experience, users don't see the little excel icon anyway on the title bar, so instructing them to right click/export is probably going to work.