Issue converting the values from Scientific E-notation

[VijayGopal]

Hello Community,

I need help in converting below values with E notation:

-5.9604644775390625E-8
-7.4505805969238281E-9
5.9604644775390625E-8
-5.9604644775390625E-8
2.9802322387695313E-8
2.9103830456733704E-11
-1.1059455573558807E-9
1.862645149230957E-9

Any help is much appreciated!

[alexnajm · Accepted solution]

The ToNumber function works fine here! These are very small number so they won't display, but they are there

[apathetichell · Accepted solution]

tonumber(regex_replace([_Currentfield_],"^(-*\d\.\d+)(E.*)$","$1"))*(pow(10,
tonumber(regex_replace([_Currentfield_],"^(-*\d\.\d+)E(-*\d+)$","$2"))))

use a new fixed_decimal field (say 39.36)-  the key part is the part after the .... the above is written for a multi-field formula - but you could create a new column in a generic formula.

@alexnajm 's formula works as well - I'd recommend using a fixed decimal here - not a double or float.

[VijayGopal]

Hi,

I tried both the solutions and able to see the correct values but its showing null for values without E notation.

[apathetichell]

mine would need to be wrapped in a contains.

if contains([_CurrentField_],"E") then 

tonumber(regex_replace([_Currentfield_],"^(-*\d\.\d+)(E.*)$","$1"))*(pow(10,
tonumber(regex_replace([_Currentfield_],"^(-*\d\.\d+)E(-*\d+)$","$2"))))

 else tonumber([_CurrentField_] endif

not sure why @alexnajm 's wouldn't work for your data.

[alexnajm]

If you have tried both solutions, please send us a sample data set and workflow with the two options so @apathetichell and I can troubleshoot - getting a screenshot of the output doesn't show us if the configuration was correct!

[VijayGopal]

Thank you so much, here is the sample data.

[alexnajm]

My above solution worked perfectly when testing on your data - what version of Alteryx are you using?

[VijayGopal]

I'm on 2023.1 version 

[alexnajm]

Ok - did the workflow work then?