Alteryx Designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.

E notation

brad_j_crep
8 - Asteroid

I'm bringing in an excel document with numbers having 20 places after the decimal point.  These numbers are displayed with E notation (-8.2935787953867923E-3).  There only needs to be two decimal places.  I can't change the excel document so how do I either bring in the numbers without the notation or change the notation afterwards? 

 

If anyone has a fix I'd really appreciate it!

 

Thanks!

6 REPLIES 6
MarqueeCrew
20 - Arcturus
20 - Arcturus

Have you considered using a select tool and changing the data type to:  FIXED DECIMAL?

 

if the numbers are all less than 0, you could use 4.2.

 

Thanks,

 

Mark

 

P.S.  If this doesn't solve the problem for you, could you OPTIONS-Export the module and include some sample data?

Alteryx ACE & Top Community Contributor

Chaos reigns within. Repent, reflect and restart. Order shall return.
Please Subscribe to my youTube channel.
dataMack
12 - Quasar

If it's just formatting in the Excel document, then you shouldn't have any trouble when Input into Alteryx as it will read the actual value stored in the cell.

 

If you are saying the the scientifc notation is actually text stored in the cell (ie, when you click on the cell and inspect the formula bar you see the 1.23E-3 instead of 0.00123 then you'll have to convert that text within Alteryx.  But don't worry- it's real easy- you basically just move the decimal based on the exponent value.

 

To do that, build a formula to find the 'E' and then use the exponent (after the E) to determine how many zeros and which direction (negative goes left, positive goes right) you need to place the decimal.  In my example above 1.23E-3, you need to move the decimal left since the exponent is negative, and you'll move it 3 spaces.  so your resulting number is 0.00123.

martinbosak
6 - Meteoroid

I just tried to use Formula with Expression:

 

ToNumber("-8.2935787953867923E-3")

 

So i suppose that fields from excel will have String format, just using this fields in formula would help.

 

Martin

brad_j_crep
8 - Asteroid

Hey Mark, there's really not much to send.  I'm input excel worksheets(think of a nicely dressed up excel worksheet with all it's formatting and hidden tables), pulling out specific data and exporting it to a summary on another excel worksheet.  My problem is that even if I change to FixedDecimal after I do a ToNumber all I get is a Null statement.  I thought about the macro idea before but was hoping for something simpler I was overlooking.  The attached excel document is something like I'd see in a line from the original excel document.

 

Thanks for any help!

tom_montpool
12 - Quasar

The MultiField Formula tool would be the quickest way to fix your file...

 

ENotation.JPG

GGuenther
7 - Meteor

Here is the view to try if your incoming data is a text vs numeric.

MultiScientific.JPG Be sure to set up the Fixed Decimal to your data needs as well. This is an area I'm still working on.

Labels