Data from Excel with E Notation

[craigja]

Hi,

I have an excel file that shows 8.33% and when I click in the cell Excel shows the full value as 8.32880188202969%.  When I bring the file into Alteryx it shows the value as 8.3288018820296905E-2, when looking at the Select tool it shows the Type of the field as String, when looking at the type of the field in Excel it doesn't seem to be defined.

I dont think I can change the Excel file since its being generated each quarter, how can I deal with this in Alteryx?  Do I need to have some formula to strip out after the E and then multiple the remaining number by the value of E?

[OllieClarke]

Hi @craigja what happens if you change the data-type to a double with a select tool?

[OllieClarke]

@craigja alteryx should happily convert the scientific notation to numeric so a simple select tool should solve you here :)

[Akash__on · Accepted solution]

Concept : In Alteryx as you can deal with all types of data formats in the field. Numeric, string, date and time, spatial, boolean

Use can use formula tool to convert the data type easily even in some cases select and union tool helps in changing the data type. try them when you use them. 

here in this example as the data input was in string first change it to "double" as the size is big using above mentioned tool.

next, using formula tool and some type casting - desired result is obtained

hoping this helps

[atcodedog05]

Hi @Akash__on 

Interesting, what does smartround() do?

[Akash__on]

@atcodedog05 

SMARTROUND(x) - Return [x] rounded to nearest multiple of a value determined dynamically based on the size of [x]

• it helped me to round the long decimal format number into a rounded required number.

generally I use "{:,2f}".format(value) in python for rounding to desired decimal places but in Alteryx I use smart round or round on numeric values, or sometime string functions (after converting it to string) if possible

[craigja]

at replies as always folks!  Thats it sorted by using smartround

[craigja]

No wait!  Doesnt work - look at row 12 for example, that should round to 16.31% not 16.25%, Row 10 which is onw with the E notation, should round to 5.27 not 5.3

[Akash__on]

@craigja 

use this instead in the formula tool

data type as fixed decimal.

I hope this will work :)

[craigja]

yeah that fixes it, but what if its E-3?  I think what I need to do is somehow get the number after the E then multiple it by that many 10's - so E 2, would be *100 E 3 would be *1000