topic Re: Leading zeros problem in App Development

Leading zeros problem

ali_hijazi — Thu, 14 Apr 2022 11:13:50 GMT

Hello,
I'm reading data from an oracle database and from 2 tables (GACCENTRYD, and ATEXTRA) a fact and a dimension table respectively

there is a field that contains values like 60000, 0302008, 000701, 42A000, etc....
what is the approach to have the values read by Qlik as they are in the database?
I have a field called Ident2_0 in ATEXTRA table

the value 0302008 is coming as 302008
the value 000701 comes as 000701

if in the load statement I use text(Ident2_0) then
the value 0302008 comes as 0302008
the value 000701 comes as 701 !!!

if in the sql part of the load statement I use to_char function then
the value 0302008 comes as 302008
the value 000701 comes as 000701

Re: Leading zeros problem

hic — Thu, 14 Apr 2022 12:56:44 GMT

This is most likely a connector setting/problem. The Qlik engine does not reformat any number, unless you specify that it should.

Also, the fact that the Load statement doesn't change the format when you use the TO_CHAR function in the SELECT statement shows this.

Re: Leading zeros problem

ali_hijazi — Thu, 14 Apr 2022 13:00:11 GMT

I'm using OLEDB connector
is there a setting that solves my problem?

Re: Leading zeros problem

hic — Thu, 14 Apr 2022 13:31:36 GMT

I don't know. You'll have to check the ORACLE OLE DB documentation.

But it seems to me as if the OLE DB connector reformats the value if it thinks it is a number, and then the Qlik engine gets 701. But when you use the TO_CHAR function, the connector treats it as a string, and then the Qlik engine gets 000701.