topic Re: Convert scientific/exponential format to number value in QlikView

Convert scientific/exponential format to number value

richard_chilvers — Wed, 20 Jan 2016 10:03:14 GMT

My CSV file contains phone numbers in the scientific format, eg.

441642318911 appears as 4.42E+11

Within Excel I can see the correct phone number but everything I have tried in QV gives me a value of 441000000000.

I haven't been able to find a solution on this forum - any ideas?

Thanks

Re: Convert scientific/exponential format to number value

arulsettu — Wed, 20 Jan 2016 10:08:40 GMT

try this

Num(Replace('4.42E+11', ',', '.'), '(dec)')

check this doc also

Conversion of Exponential Notation to Decimal numbers

Re: Convert scientific/exponential format to number value

richard_chilvers — Wed, 20 Jan 2016 10:19:35 GMT

I still get the same result ie. 441000000000

Re: Convert scientific/exponential format to number value

arulsettu — Wed, 20 Jan 2016 10:21:58 GMT

can you upload sample excel and with expected output

Re: Convert scientific/exponential format to number value

richard_chilvers — Wed, 20 Jan 2016 10:28:05 GMT

Sample attached.

I would expect the first value of [Cust phone] to be 441642318911.

Thanks

Re: Convert scientific/exponential format to number value

HirisH_V7 — Wed, 20 Jan 2016 10:28:26 GMT

Hi ,

Check this

data:
LOAD * ,
   Num(Data, '(dec)') AS Decimal
INLINE [
Data
 4.42E+11
];

Output:

HTH,

Hirish

Re: Convert scientific/exponential format to number value

arulsettu — Wed, 20 Jan 2016 10:30:17 GMT

no file attached

Re: Convert scientific/exponential format to number value

richard_chilvers — Wed, 20 Jan 2016 10:31:50 GMT

Re: Convert scientific/exponential format to number value

arulsettu — Wed, 20 Jan 2016 10:35:02 GMT

this one works for me try this in your load

LOAD [CDR Id],

[Daily Call Id],

Num(Replace([Cust phone], ',', '.'), '(dec)') AS phone

from.......

Re: Convert scientific/exponential format to number value

matiasinfantes — Mon, 10 Dec 2018 15:51:41 GMT

Thank you! It resolved my problem with this number 1120180701030000, it was loaded as 1,12018070103e+15. I applied the num function with '(dec)' and it returned the correct number 1120180701030000