Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
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'))
Text(Fieldname) will typically do the trick.
I can't get that to work since I load my data with a select statement. It wont accept Qlik syntax.
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.
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.
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];
That doesn't do the trick. It seems that Qlik already discards the preceding zeros at the first load statement.
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'))
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.