topic How to convert an exponential no to decimal in QlikView

How to convert an exponential no to decimal

kamalqlik — Mon, 12 Sep 2011 06:41:35 GMT

Hi friends, I m facing a problem .Please suggest me the solution for the problem.

hey guys,how to convert an exponential number to decimal in

qlikview?

i tried mantissa*pow(10,exp) formula but mantissa that i

separate from the original field

,it is showing that it is neither a text nor a number.

Thanks in advance

Kamal Naithani

How to convert an exponential no to decimal

kaushiknsolanki — Mon, 12 Sep 2011 07:38:32 GMT

Hi,

Its very simple. Go to the Settings -> Document Properties -> Number Tab-> Click on the field which have Exponential value and then click on Number.

Regards,

Kaushik Solanki

How to convert an exponential no to decimal

Fri, 27 Jan 2012 09:22:04 GMT

That does not work for this format:

+1.961776E+03

+1.855197E+04

-1.304177E+07

-7.642861E+06

I need to read in these as number, can someone help me?

How to convert an exponential no to decimal

kaushiknsolanki — Fri, 27 Jan 2012 09:30:00 GMT

HI,

Try this.

Data:
load * inline [
NUMBERS
+1.961776E+03
+1.855197E+04
-1.304177E+07
-7.642861E+06
];

Dat1:
Load Num(num#(NUMBERS)) as Num Resident Data;

Regards,

Kaushik Solanki

How to convert an exponential no to decimal

Fri, 27 Jan 2012 09:55:43 GMT

Sorry, im a complete qlikview noob, can you modify this for me sow it reads in the exponetioal values: (meas, mean, lower, upper)

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ #.##0,00-';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

LOAD @1,

@2 as name,

@3,

@4,

@5 as meas,

@6 as mean,

@7 as lower,

@8 as upper,

@9,

@10,

@11,

@12,

@13,

@14,

@15,

@16,

@17,

@18,

@19

FROM

"C:\Documents and Settings\a.lam\Desktop\temp\A000*"

(txt, codepage is 1252, no labels, delimiter is '|', msq);

How to convert an exponential no to decimal

kaushiknsolanki — Fri, 27 Jan 2012 09:58:34 GMT

Hi,

SET ThousandSep='.';

SET DecimalSep=',';

SET MoneyThousandSep='.';

SET MoneyDecimalSep=',';

SET MoneyFormat='€ #.##0,00;€ #.##0,00-';

SET TimeFormat='h:mm:ss';

SET DateFormat='DD-MM-YYYY';

SET TimestampFormat='DD-MM-YYYY h:mm:ss[.fff]';

SET MonthNames='jan;feb;mrt;apr;mei;jun;jul;aug;sep;okt;nov;dec';

SET DayNames='ma;di;wo;do;vr;za;zo';

LOAD @1,

@2 as name,

@3,

@4,

num(num#(@5)) as meas,

num(num#(@6)) as mean,

num(num#(@7)) as lower,

num(num#(@8)) as upper,

@9,

@10,

@11,

@12,

@13,

@14,

@15,

@16,

@17,

@18,

@19

FROM

"C:\Documents and Settings\a.lam\Desktop\temp\A000*"

(txt, codepage is 1252, no labels, delimiter is '|', msq);

Regards,

Kaushik Solanki

How to convert an exponential no to decimal

Fri, 27 Jan 2012 10:03:13 GMT

Thank you for your help, but.. now the values are gone, it shows "-" in my table object.

How to convert an exponential no to decimal

kaushiknsolanki — Fri, 27 Jan 2012 10:10:06 GMT

HI,

Can you send me the txt file.

Regards,

Kaushik Solanki

Re: How to convert an exponential no to decimal

Fri, 27 Jan 2012 10:12:10 GMT

Hello, thanks again for your efford!. The file is attached.

Best Regards,

André Lam

How to convert an exponential no to decimal

kaushiknsolanki — Fri, 27 Jan 2012 10:36:55 GMT

HI,

Its working my side.

Regards,

Kaushik Solanki

How to convert an exponential no to decimal

Fri, 27 Jan 2012 10:58:43 GMT

But what can i do?, i used the code from your post..

Re: How to convert an exponential no to decimal

Fri, 27 Jan 2012 20:24:33 GMT

what version are you on ?

Re: How to convert an exponential no to decimal

Mon, 30 Jan 2012 08:30:20 GMT

11.00.11154.0

I got it working:

Num(Left(@5, Index(@5, 'E') -1) * Pow(10, TextBetween(@5, 'E', ''))) as meas,

Num(Left(@6, Index(@6, 'E') -1) * Pow(10, TextBetween(@6, 'E', ''))) as mean,

Num(Left(@7, Index(@7, 'E') -1) * Pow(10, TextBetween(@7, 'E', ''))) as lower,

Num(Left(@8, Index(@8, 'E') -1) * Pow(10, TextBetween(@8, 'E', ''))) as upper,

For now i'm happy, although it would have been easier to just say "this is a number".....