Issue: Decimals getting converted to exponential format.

[Anonymous]

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 !

[MindaugasBacius]

We have faced the similar issue.

I suggest simple use * 1 multiplicator:

Load

     Field * 1 as Field

From;

[Anonymous]

Assuming I understood your suggestion correctly, this is what i tried:

     if( IsNum(ColName),  ColName * 1,  ColName ) as ColName2

It does not seem to work, even after multiplying with 1, it is loading -1e-06 as before for -0.000001.

Please do let me know if you see any gaps here.

[jaibau1993]

Hi Aachin. I am not quite proud of my solution but here it goes:

I created a toy Excel file with a column with format general with rows

0,0000006

-0,00000085

Null

43246

error

There is text, dates and small numbers. My load script is as follows:

LOAD

if(IsNum(Column) and Column like '*e*', Num(Column, '#.###,' & Repeat('0', fabs(SubField(Column, 'e', -1))+if(Column like '*,*', Len(TextBetween(Column, ',', 'e')), 0))), Column) as Column

FROM
[.\DudaNumExp.xlsx]
(ooxml, embedded labels, table is Hoja1);

First I check if the field is a number and if it has been read in exponential form, thus the condition Column like '*e*'. If so, I format the data with Num function, forcing its representation with the precision required. That precision is "the number after the 'e' letter plus the number of decimals in exponential form". This is done in

Repeat('0', fabs(SubField(Column, 'e', -1)) //NUMBER AFTER 'E' LETTER

+ if(Column like '*,*', Len(TextBetween(Column, ',', 'e')), 0)) //NUMBER OF DECIMALS

This solutions works fine for me but, as I said, I'm not proud, looks pretty complicated. You could use something like

Num(Column, '#.###,0000000000000000')

instead if you don't mind to fix the number of decimals after comma.

Hope it helps! Bests,

Jaime.

[jonathandienst]

How about something like this:

=Num(value, '0.0' & Repeat('0', -Log10(value)))

=Num(1e-6, '0.0' & Repeat('0', -Log10(1e-6)))  -> 0.0000010

[marcus_sommer]

To get an individual numeric formatting for each value (you might need some additional logic to differ for various numeric content like dates & times, integer and floats and also for strings or any kind of NULL's) you could use something like this:

Num(ColName, '#,##0.' & repeat('#', len(frac(ColName)))) as ColName

- Marcus

[Anonymous]

Thanks Jonathan !

Not all numbers are numeric, its a mixed data set. Also, the decimal numbers are being read in differently. Some are getting converted to exponential form while others are still being treated as decimals.

[Anonymous]

Thanks Marcus !

If I use similar implementation, even some numbers that have only 2 decimal places in original data set [say 12345.99] are getting converted to numbers with a lot more precision [say 12345.989999999]. This is a common observation for all implementations where I try to enforce the precision.

Not sure how its picking up the extra set of numbers.

[jaibau1993]

Please, note that I used decimal comma instead of decimal point. If you need a translation:

LOAD

if(IsNum(Column) and Column like '*e*', Num(Column, '#,###.' & Repeat('0',fabs(SubField(Column, 'e', -1))+if(Column like '*.*', Len(TextBetween(Column, '.', 'e')), 0))), Column) as Column

FROM
[.\DudaNumExp.xlsx]
(ooxml, embedded labels, table is Hoja1);

Bests,

Jaime.

[marcus_sommer]

I think this is a bit different to the above mentioned formatting-issue because it's related to the way how Qlik worked with the numbers which is a binary and not a decimal system and therefore for many numbers didn't exists an exact binary value. This meant it's a rounding issue and quite well explained here: Rounding Errors.

For many use-cases it's not very important because for the usual measures in Qlik like amounts, currencies and also various rates on them is the precision more as enough. If you really needs calculations with large numbers ( >= 15 digits ) and/or with a very high precision you need to use a different tool for it maybe transferring it to R and displaying just the results in Qlik.

Beside this there could be differences between the stored values respectively the in calculations used values and those ones of them which are displayed in any object. If you want directly to compare the input-values with the stored values you need to look on them with fieldvalue() by loading the values without any transformation.

I don't know what your aim is on these data but I could imagine that it might be useful to load the different data-types with different loadings by using appropriate where-clauses on each part and concatenating/joining/mapping them afterwards again (if necessary).

- Marcus