Skip to main content
Announcements
See what Drew Clarke has to say about the Qlik Talend Cloud launch! READ THE BLOG
cancel
Showing results for 
Search instead for 
Did you mean: 
CasperQlik
Creator
Creator

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
Creator
Creator
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
Creator
Creator
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
Creator
Creator
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
Creator
Creator
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.