Exporting mixed numeric formats from Qlik Cloud

[Daniele_Purrone]

Data values in Qlik Sense are stored as dual values, which means that they have an underlying numerical value and a textual presentation value. This is important to understand before (re)formatting values.

• An integer has an underlying numerical value (of max 14 digits) without decimal digits. The presentation value can have a thousand separators in addition to the integer digits, but it may also be without separators. 
• A decimal values has an underlying numeric value with both integer and decimal values. The numeric value is stored as a floating-point value. See article links for more details. The presentation can have a have a decimal and a thousand separators.
• Zero values that are stored as integers are exactly zero. Zero values that are stored as decimal values may not always have a underlying zero value, even if the presentation is zero.

Num(number, format) format function can be used to load a numeric value, number, and store it as a dual value where the presentation is stored according to the format.

For example, Num(1050.75, ‘#,##0.0’) will be stored as numeric value 1050.75 with the presentation rounded to one decimal value as 1,050.8.

The general numeric precision in Qlik Sense is 14 digits. By using a format string with 14 # characters, the engine will interpret the number as a generic 14-digit value.

For example, Num(1050.75, ‘##############’) will be stored as numeric value 1050.75 with the presentation sustained as the input value 1050.75.

The num #(text, format) function allows reverse formatting, where a text is parsed according to the format string as a numeric value. The text value becomes the presentation value and the interpreted numeric value is stored as the underlying value.

How does Qlik Cloud handle mixed formats?

In Qlik Cloud, the reporting service requests the visualization data from the Qlik Engine. In this data set, each column has one format stored by the engine, which is used by the reporting service to format columns in the exported Excel sheet.

Columns with mixed formats (numbers and text) will be treated as undefined, and can be expected as text in the exported Excel.

Columns with mixed numeric formats will be exported in a uniform format. Columns formatted with ############## are interpreted as generic numeric values by the reporting service. The output format in Excel in this case becomes General.
 
There are two ways to apply the generic format to mixed numeric fields or chart columns:

• Format the data during load in the load script.
• Format the measure expression on object level.

There is naturally a maintenance benefit in controlling formats as much as possible in the script so that object-level formatting can be avoided to the most possible extent. 

For data processed through the QVD layer, it may be a further optimization to format the data closer to the source to benefit from the preformatted data as it is ingested through an ETL flow.

References:

• Qlik Sense formatting functions: Formatting functions (help.qlik.com)
• Num() formats a number: Num - script and chart function (help.qlik.com)
• Num#() interprets text as numerical value: Num# - script and chart function (help.qlik.com)
• Number formats: Descriptions for number and time formats (help.qlik.com)
• Decimal (floating point) values in Qlik Sense: Decimal Values In Qlik Sense And QlikView - June 2019 and later (Qlik Support Article)

Environment

• Qlik Cloud