Skip to main content
Announcements
Qlik Connect 2024! Seize endless possibilities! LEARN MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
CasperQlik
Contributor III
Contributor III

Stop Qlik from removing proceeding zero(s)

Hi Qlik community

I have a problem when loading id fields that contain one or more zeros at the start. Qlik removes all proceeding zeros so that id = 012345 becomes 12345.

I load from an Oracle database with a select statement that we set up with QlikSense connectors.

Any advice to stop this behavior is appreciated.

//Casper

2 Solutions

Accepted Solutions
hic
Former Employee
Former Employee

Try a preceding Load:

Load Text(Field) as Field, ... ;
SELECT Field, ... FROM ... ;

If that doesn't work, then the output from the SELECT doesn't have the leading zeros. Which means that the Connector / ODBC driver removes them, and not the Qlik engine.

View solution in original post

Or
MVP
MVP

A quick workaround could be to concatenate a non-numeric character to this field in your SQL - e.g. a || YourColumn as YourColumn - and then in the load script use Text(PurgeChar(YourColumn,'a'))

View solution in original post

8 Replies
Or
MVP
MVP

Text(Fieldname) will typically do the trick.

CasperQlik
Contributor III
Contributor III
Author

I can't get that to work since I load my data with a select statement. It wont accept Qlik syntax. 

hic
Former Employee
Former Employee

Try a preceding Load:

Load Text(Field) as Field, ... ;
SELECT Field, ... FROM ... ;

If that doesn't work, then the output from the SELECT doesn't have the leading zeros. Which means that the Connector / ODBC driver removes them, and not the Qlik engine.

CasperQlik
Contributor III
Contributor III
Author

Hi Hic

I tried the preceding load with no success.

It appears I will have too examine the Connector.

Thank you for your input - I will report back if I find a solution.

 

Cascader
Creator
Creator

 

try 2 step loading to force qliksense to foramt ID as text: 

LOAD id_text ; SQL SELECT id_field FROM your_table ;

 

LOAD Text(id_text) AS id_text_resolved RESIDENT [YourTableName];

CasperQlik
Contributor III
Contributor III
Author

That doesn't do the trick. It seems that Qlik already discards the preceding zeros at the first load statement.

Or
MVP
MVP

A quick workaround could be to concatenate a non-numeric character to this field in your SQL - e.g. a || YourColumn as YourColumn - and then in the load script use Text(PurgeChar(YourColumn,'a'))

CasperQlik
Contributor III
Contributor III
Author

Your solutions are valid. I made a blunder when checking my data against the source location. The source that I connect to actually has the proceeding zeros removed already before I connect with Qlik. I was looking and comparing with a different table with the zeros still in place.