Issue converting the values from Scientific E-notation

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!

Transformation

Accepted answers

alexnajm

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

Issue converting the values from Scientific E-notation.png

apathetichell

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.

All comments

alexnajm

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

Issue converting the values from Scientific E-notation.png

apathetichell

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.

Capture.JPG

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.

sample data.xlsx

alexnajm

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

Issue converting the values from Scientific E-notation.yxmd

VijayGopal

I'm on 2023.1 version

alexnajm

Ok - did the workflow work then?

apathetichell

Hey - issue could be with your testing data not being replicated in this thread (ie upload more sample data in the same format as your actual data) or 2023.X version issues. I don't work with 2023.X so @alexnajm would have to test that.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703