Do not input private or sensitive data. View Qlik Privacy & Cookie Policy.
Skip to main content

Announcements
Qlik Open Lakehouse is Now Generally Available! Discover the key highlights and partner resources here.
cancel
Showing results for 
Search instead for 
Did you mean: 
ali_hijazi
Partner - Master II
Partner - Master II

Leading zeros problem

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

ali_hijazi_0-1649934206826.png

 

 

I can walk on water when it freezes
Labels (2)
3 Replies
hic
Former Employee
Former Employee

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. 

ali_hijazi
Partner - Master II
Partner - Master II
Author

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

I can walk on water when it freezes
hic
Former Employee
Former Employee

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.