Qlik Community

Ask a Question

Knowledge

Search or browse our knowledge base to find answers to your questions ranging from account questions to troubleshooting error messages. The content is curated and updated by our global Support team

Announcements
Support Cases coming to Qlik Community Oct. 4! Start chats, open cases, explore resources. Prep for the big move: READ DETAILS

Leading zeros are missing in loaded data values

ToniKautto
Employee
Employee

Leading zeros are missing in loaded data values

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.

missing leading zeros.png

Environment:

QlikView 
Qlik Sense Enterprise on Windows 
Qlik Sense Enterprise SaaS 
Qlik Sense Business 


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
];

 

 

  1. The first value in F1 is interpreted by Qlik as a numerical 1
  2. The first value is stored as numerical value 1 with textual presentation 1
  3. The second value in F1 is interpreted by Qlik as a numerical 1
  4. 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.
  5. The third value in F1 is interpreted by Qlik as a numerical 2
  6. 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.
  7. The fourth value in F1 is interpreted by Qlik as a numerical 2
  8. 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.
  9. The loaded data will visually not look like the data source. 

    missing leading zeros.png

 

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. 

visual values identical to data source.png

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

Labels (1)
Version history
Revision #:
4 of 4
Last update:
‎2021-05-10 08:33 AM
Updated by:
 
Contributors