Export data from Qlik Sense to Xlsx in the correct formatting
When I set an appointment with you at four, you assume without asking me that I intend to sit down with you at 4 PM (We both need to sleep at 4 AM, right?) certainly you will not call me to find out if when I said ‘four’ I meant the number of the participants in the meeting or November 4th.
As human beings, we make contexts in a logical and probabilistic way that part of it is derived from our past experience. However, when it comes to software we need to be very specific. What’s more, when we deal with BI systems we need to be super-specific.
As BI developers we are committed to making information accessible to users in the most reliable and accurate way. Our responsibility as developers is not only to present the data beautifully and understandably but also to take care of the correct formatting of texts, numbers, dates, etc.
Let 4 be 4
In MS Excel, we can use number formats to change the appearance of numbers, including dates and times, without changing the actual number. The number format does not affect the cell value that Excel uses to perform calculations. The actual value is displayed in the formula bar.
LogiExport allows you to control the exact format in which the data will be extracted from a pivot table or a straight table in Qlik Sense to Excel. Using Excel-supported formatting codes.
With LogiExport you can easily control the formatting of the values.
Automate Excel formatting codes
Let’s see how to control values format using LogiExport with a few simple examples:
1. Formatting dates
In the marked box, you can see an example of how to set a date format for a column.
2. Formatting numbers
In this example, you can see how to set a number format
3. Strings Formatting
In this example, you can see how to set a format for string values
Solve Null exporting
If you’ve been this far, we have a special trick that you must hear about. many Qlik users complain that when exporting a Null value to Excel it is displayed as a “-” string rather than an empty value. Sometimes we will want to empty the cells.
So, let’s solve it with an API snippet:
1. Head to the API box
1. Type in this code:
='workbook.find(/^-$/g, match => "");'
2. Now when you click the LogiExport button the file will be exported without the nulls sign “-“.
3. You can use any Regex expression to replace values in the exported file.
For more information and documentation about using the API:
