topic Re: Issue: Decimals getting converted to exponential format. in QlikView

Issue: Decimals getting converted to exponential format.

Anonymous — Tue, 21 Aug 2018 08:47:41 GMT

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 !

Re: Issue: Decimals getting converted to exponential format.

MindaugasBacius — Tue, 21 Aug 2018 09:10:15 GMT

We have faced the similar issue.

I suggest simple use * 1 multiplicator:

Load

Field * 1 as Field

From;

Re: Issue: Decimals getting converted to exponential format.

Anonymous — Tue, 21 Aug 2018 09:39:34 GMT

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.

Re: Issue: Decimals getting converted to exponential format.

jaibau1993 — Tue, 21 Aug 2018 11:33:48 GMT

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.

Re: Issue: Decimals getting converted to exponential format.

jonathandienst — Tue, 21 Aug 2018 11:45:08 GMT

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

Re: Issue: Decimals getting converted to exponential format.

marcus_sommer — Tue, 21 Aug 2018 12:01:42 GMT

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

Re: Issue: Decimals getting converted to exponential format.

Anonymous — Wed, 22 Aug 2018 06:41:26 GMT

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.

Re: Issue: Decimals getting converted to exponential format.

Anonymous — Wed, 22 Aug 2018 06:43:58 GMT

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.

Re: Issue: Decimals getting converted to exponential format.

jaibau1993 — Wed, 22 Aug 2018 07:35:34 GMT

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.

Re: Issue: Decimals getting converted to exponential format.

marcus_sommer — Wed, 22 Aug 2018 07:59:27 GMT

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

Re: Issue: Decimals getting converted to exponential format.

Anonymous — Wed, 22 Aug 2018 08:09:58 GMT

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 !

Re: Issue: Decimals getting converted to exponential format.

Anonymous — Wed, 22 Aug 2018 08:42:08 GMT

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 !