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

Announcements
Join us in NYC Sept 4th for Qlik's AI Reality Tour! Register Now
cancel
Showing results for 
Search instead for 
Did you mean: 
Anonymous
Not applicable

Issue: Decimals getting converted to exponential format.

Hello All,

We are loading data from an excel file. The column in question is used to store all kind of invalid values [might be a date, a string, a numeric value or even NULL]. The format for that column is specified as 'General' in the excel file.

During the load, the small decimal values [-0.000001] are getting converted to their exponential form [-1e-06]. This conversion is not desirable as we need to see the number the way it is.

Tried loading as Text, and also tried various number format but none seem to achieve the purpose. On using Num(ColName, '#,##0.0########') in the load script we are able to get the correct number but additional [non significant] zeroes are appended to the end of the number to account for the remaining '#' signs that we use [it loads -0.000001000 in place of -0.000001]

Any suggestions regarding how to load the contents the way they are in the excel file?

Thanks in advance for any pointers / suggestions

-

Thanks !

11 Replies
Anonymous
Not applicable
Author

Thanks Marcus !

Currently the aim is to display the invalid values correctly. Also, we are not crossing the 15 digit limit. I shall explore more options

Thanks again for your inputs !

Anonymous
Not applicable
Author

Thanks Jaime !

I have used the Column like '*e*' part in the existing solution and it seems to fill the missing blocks. Earlier i had issues as all values were getting modified but now only the ones that have exponential form are being changed.

For now I have modified your logic as :

if(IsNum(Column) and Column like '*e*',Replace(RTRIM(Replace(Num(Column,'##0.#############'),'0',' ')),' ', '0'),Column) as Column,

and it seems to do the trick

-

Thanks !