Stop Qlik from removing proceeding zero(s)

[CasperQlik]

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

[Or]

Text(Fieldname) will typically do the trick.

[CasperQlik]

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

[hic · Accepted solution]

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]

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]

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]

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

[Or · Accepted solution]

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]

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.