Data from Excel with E Notation

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?

Help

Accepted answers

Akash__on

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

All comments

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

%.yxzp

Akash__on

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?

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

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703