article Leading zeros are missing in loaded data values in Official Support Articles

Leading zeros are missing in loaded data values

ToniKautto — Tue, 11 Apr 2023 11:02:44 GMT

QlikView and Qlik Sense load either missing or additional zeros, compared to the values in the data source.

For example data like in the below script is loaded with incorrect leading zeros.

LOAD * Inline [ F1, F2 1, a 01, b 02, c 2, d ];

On the second row the leading zero is missing, and on the fourth row, there is an unexpected additional leading zero.

Environment:

Cause:

The observed behaviour is a part of fundamental Qlik engine functionality and data compression technique.

In short, this means that Qlik interprets and associates loaded data by best effort. As part of Qlik optimization and compression technique the same reoccurring value in a field is stored on the first occurrence only, and the following occurrence reference to the first (stored) value. This means that two values with identical numerical representation can not be stored with two different textual presentations in the same field in Qlik.

Please keep in mind that all numerical values in Qlik act as dual values. This means that they have an underlying numerical value that is used for calculations, and a textual formatted value which is the value seen in charts or as calculations results.

In the example below four rows of data are loaded. In the field F1 there are four different value.

LOAD * Inline [ F1, F2 1, a 01, b 02, c 2, d ];

The first value in F1 is interpreted by Qlik as a numerical 1
The first value is stored as numerical value 1 with textual presentation 1
The second value in F1 is interpreted by Qlik as a numerical 1
There is already a numerical value 1 stored in the field F1, so Qlik associates the second occurrence to the first value. This means that the value 01 will be associated as a numerical 1 with presentation value 1.
The third value in F1 is interpreted by Qlik as a numerical 2
There is no other value stored in the field F1 as a numerical 2, hence this value is stored as numerical 2 with the textual presentation value 02.
The fourth value in F1 is interpreted by Qlik as a numerical 2
There is already a numerical value 2 stored in the field F1, so Qlik associates the second occurrence to the first value. This means that the value 2 will be associated as a numerical 2 with presentation value 02.
The loaded data will visually not look like the data source.

Resolution:

Identical numerical values are associated with the same textual presentation value in Qlik . For this reason, it is not possible to store the same numerical value with different textual presentation values.

One way to resolve this is to format the values as text by using Text() during reload.

LOAD text(F1) as F1, F2 Inline [ F1, F2 1, a 01, b 02, c 2, d ];

This will result in the visual values looking identical to the data source.

NOTE: Value formatted with Text() will not have underlying numerical values, hence calculations on these values is not possible.

Re: Leading zeros are missing in loaded data values

p_verkooijen — Mon, 19 Jun 2023 07:18:35 GMT

Any chance this behavior will ever be "fixed"?
Spend hours finding out why a match did not work and then finding a way to create a valid workaround.

Eventually used IF(LEN(EANcode)<>LEN(NUM(EANcode)), TEXT(EANcode), EANcode) AS EANcode,

To get both values in the application.

https://community.qlik.com/t5/Connectivity-Data-Prep/Qlik-keeps-formatting-numbers-based-on-first-match/td-p/2084295

Re: Leading zeros are missing in loaded data values

Sonja_Bauernfeind — Mon, 19 Jun 2023 12:55:33 GMT

Hello @p_verkooijen

We are unaware of a plan to change the behaviour. If you want to raise awareness with our product team to have this reviewed, I suggest raising an idea here: Ideation.

All the best,
Sonja

Re: Leading zeros are missing in loaded data values

SamKng — Fri, 22 Mar 2024 09:13:04 GMT

We've used to deal with this issue as described above in our on-prem Qlik Enterprise landscape. Any recommendation how to overcome this issue when loading data via Qlik DataTransfer into cloud? (no possibility to edit load statement to add text()-formula...)

Highly appreciate any tips...

Best regards Sam

Re: Leading zeros are missing in loaded data values

Sonja_Bauernfeind — Mon, 25 Mar 2024 10:31:09 GMT

Hello @SamKng

For detailed assistance with this, please post your requirements directly in the Qlik Sense Data Connectivity forum, where our active support agents and your knowledgeable Qlik peers can assist you.

All the best,
Sonja

Re: Leading zeros are missing in loaded data values

Fabiano_Martino_Intelco — Tue, 24 Sep 2024 07:25:51 GMT

Hi,

I'm managing an ETL app where a lot of fields need to be treated as text.

Since the load is based on a simple SELECT * I would need to change every Load * to a list of fields with the Text() function.

Moreover, additional fields are planned to be added in the future to the DB.

Is there a way to avoid this changes?

A global setting to be set in the initial part of the main script would be the ideal solution.

Thanks

Re: Leading zeros are missing in loaded data values

Sonja_Bauernfeind — Tue, 01 Oct 2024 12:08:09 GMT

Hello @Fabiano_Martino_Intelco

For detailed assistance, please post your requirements directly in the Qlik Sense Data Connectivity forum, where our active support agents and your knowledgeable Qlik peers can assist you.

All the best,
Sonja

Re: Leading zeros are missing in loaded data values

MildaV — Mon, 31 Mar 2025 05:34:13 GMT

Hello,

could you give us a hint, why the decribed behaviour occurs not in all cases. We made a test with exactly same data uploaded into 2 sources: MS SQL Server and Snowflake (tested field type is Varchar in both). Results are the following:

Data extracted from MS SQL Server bahaves as described in the article
Data extracted from Snowflake is NOT changed - it keeps both values with leading zeros and without, with dots at the end and without, etc.

What is the reason for this? Why does it behave differently? Why is described "fundamental Qlik engine functionality and data compression technique" not working in all cases? What does it depend on?

Thank you,

Milda