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

Announcements
Qlik GA: Multivariate Time Series in Qlik Predict: Get Details
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.