E Notation issue

Hello,

Has anyone experienced E Notation issues when the data is displayed from Alteryx to Excel?

Long story short, I have a calculated field in Alteryx which results in a string data 1.29341792284571e-11. Before exporting that to excel, I use a select tool to make it double(also both fixed decimals and tonumber) and in Alteryx it gets fixed (result 0), but exported to excel it displays 1.29341792284571e-11 nonetheless.

Now, I searched the community and I found out that this is an Excel wonder, but how do I get it fixed?

Any ideas?

Thanks a lot!

Irina

Topic of Interest

Accepted answers

Amol_Telore

Hey @IrinaManta

As @IraWatt mentioned, Alteryx doesn't show the entire number in the result window but in the background, it stores the complete figure and so it won't affect your calculations.

However, if your requirement is to show a complete numbers then you may use the below formula as a workaround to show the numbers.

tonumber(REGEX_Replace(tostring([Field_1]),'(^[\d.]+)e(-?\d+)','$1')) * 
POW(10, tonumber(REGEX_Replace(tostring([Field_1]),'(^[\d.]+)e(-?\d+)','$2')))

The above formula in nutshell = 1.29341792284571 * 10^-11

This way you will be able to see number upto certain extent W/o any scientific notation. Only drawback of this is data after decimal point is being rounded off due to large number while displaying it in result window but it has complete number in the backend.

All comments

IraWatt

Hey @IrinaManta,

Double click the cell to see the more details. The red triangle in the corner of the cell indicates that there is more information that cant fit in the cell:

More information here: Results Window | Alteryx Help

Any questions or issues please ask
HTH!
Ira

IrinaManta

Hey @IraWatt ,

Much appreciated the info

How do I fix it thought? I need to bring it to the actual number with around 5 decimals.

Thanks!

Irina

IraWatt

@IrinaManta you cant really show 1.29341792284571e-11 to 5 decimals as it would just be 0.00000 as the actual number is 0.00000000000129341 ....

Alteryx is storing the actual number as I showed when you double click on the cell. It just shows 0 as there is not space in the results window to show all the numbers.

Amol_Telore

Hey @IrinaManta

As @IraWatt mentioned, Alteryx doesn't show the entire number in the result window but in the background, it stores the complete figure and so it won't affect your calculations.

However, if your requirement is to show a complete numbers then you may use the below formula as a workaround to show the numbers.

tonumber(REGEX_Replace(tostring([Field_1]),'(^[\d.]+)e(-?\d+)','$1')) * 
POW(10, tonumber(REGEX_Replace(tostring([Field_1]),'(^[\d.]+)e(-?\d+)','$2')))

The above formula in nutshell = 1.29341792284571 * 10^-11

IrinaManta

Hi @IraWatt - thanks a lot for clarifying for me how the E-Notations works, really helpful.

@Amol_Telore - yes indeed, I wanted to see the whole number in excel because I have some cases where the number transformed is not 0.000000 but 0.035553 for example (below). So thanks lot for the formula, works like a charm

From this

To this - using your Regex formula

Amol_Telore

Hey @IrinaManta

Glad this solution worked for you but i see your numbers are getting trimmed due to data type length issue.

You can change the data size of fixed decimal to show more numbers after decimal points.

Set Precision to 50 and Scale to 30

It means to will show 50 digits including decimal point. and by specifying scale to 30, you are increasing the length of digits after decimal points upto 30 digits. So that you can see complete figure. Hope your concerns are resolved.

ypt

Hi there !

Thanks for the regex formula and works wonder but I'm wondering what if my dataset contains not only e-11 but also from values from e-3 to e-11? How can I amend the formula to ensure all get changed?

Also, may I trouble you to breakdown the regex formula for better understanding?

Many thanks!

Amol_Telore

@ypt Do you have any sample dataset for your use case.

Quick Links

This months top contributors

LanisC 335

hhaigh1 280

erich_shiino 280

FláviaB 262

JORGE4900 235