Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

E Notation issue

IrinaManta
6 - Meteoroid
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? 
 
IrinaManta_0-1658223975567.png

 

IrinaManta_1-1658224002407.png

 

IrinaManta_2-1658224018780.png

 

Any ideas?
 
Thanks a lot!
 
Irina
8 REPLIES 8
IraWatt
17 - Castor
17 - Castor

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:Animation2.gif

More information here: Results Window | Alteryx Help

Any questions or issues please ask :)
HTH!
Ira

IrinaManta
6 - Meteoroid

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
17 - Castor
17 - Castor

@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 .... 

IraWatt_0-1658245279856.png

 

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
11 - Bolide

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

 

Amol_Telore_0-1658246034864.png

 

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.

 

Amol_Telore_3-1658246662312.png

 

 

IrinaManta
6 - Meteoroid

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

IrinaManta_1-1658302111143.png

 

To this - using your Regex formula

IrinaManta_0-1658302071761.png

 

Amol_Telore
11 - Bolide

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
7 - Meteor

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
11 - Bolide

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

Labels