topic Converting Expontial values into number format in QlikView

Converting Expontial values into number format

Tue, 03 Mar 2015 12:48:46 GMT

Hi QV Experts,

I have been struggling to converting the below expontial values into number format with 5 decimals values.
I have used all the possible QV functions to convert into number format but no luck.

-9,4008000000000003E-01
-9,4230000000000003E-01
-9,4306999999999996E-01
-9,5730000000000004E-01
0,0000000000000000E+00
1,0000000000000000E+00
1,0528530000000000E+01
1,0686040000000000E+01

I have checked with all below functions but no luck.

1)evaluate((-9,4008000000000003E-01)

2)num(num#(-9,4008000000000003E-01) )

3)num(num#(-9,4008000000000003E-01),'##################')

4)num((-9,4008000000000003E-01)

Please provide your valuable suggestions.

Thanks

Re: Converting Expontial values into number format

Anonymous — Tue, 03 Mar 2015 13:24:46 GMT

Thread with the same requirement http://community.qlik.com/message/17763#17763

Hope it helps.

Marc.

Re: Converting Expontial values into number format

Colin-Albert — Tue, 03 Mar 2015 13:27:10 GMT

You need to specify the decimals in your number format

num( -9.5730000000000004E-01, '#,##0.00000000')

You may also need to check your default number settings for ThousandSep and DecimalSep

The test above was done with these settings

SET ThousandSep=',';
SET DecimalSep='.';

Re: Converting Expontial values into number format

robert_mika — Tue, 03 Mar 2015 13:31:32 GMT

=left(a,len(a)-4)

The In Number tab use Number

(here a is your Field)

Re: Converting Expontial values into number format

jonathandienst — Tue, 03 Mar 2015 14:12:24 GMT

Change your environment settings with

SET DecimalSep=',';

SET ThousandSep=' '; // may not be necessary

Now the numbers should just load:

load *

Inline

[

Nbr

-9,4008000000000003E-01

-9,4230000000000003E-01

-9,4306999999999996E-01

-9,5730000000000004E-01

0,0000000000000000E+00

1,0000000000000000E+00

1,0528530000000000E+01

1,0686040000000000E+01

] (delimiter is '|');

The delimiter statement is to prevent , from being viewed as a field separator. This produces this output

-0.95730000

-0.94307000

-0.94230000

-0.94008000

0.00000000

1.00000000

10.52853000

10.68604000

HTH

Jonathan

Re: Converting Expontial values into number format

Colin-Albert — Tue, 03 Mar 2015 14:18:02 GMT

Hi Robert,

Your string slicing ignores the value of the exponential.

The values would nee to be multiplied by pow(10, exponent) to be correct

Re: Converting Expontial values into number format

Tue, 03 Mar 2015 15:09:46 GMT

Thanks all for your quick replies,but these solutions also not helped me to resolve the issue.

Re: Converting Expontial values into number format

robert_mika — Tue, 03 Mar 2015 15:15:26 GMT

Thanks Colin.

You are right

Re: Converting Expontial values into number format

robert_mika — Tue, 03 Mar 2015 15:16:04 GMT

Could you post your data set/file?

Re: Converting Expontial values into number format

Tue, 03 Mar 2015 15:21:27 GMT

Please check the attached sample file