This site uses different types of cookies, including analytics and functional cookies (its own and from other sites). To change your cookie settings or find out more, click here. If you continue browsing our website, you accept these cookies.
If you're using the Formula Tool in v11.0+, be sure to familiarize yourself with the tool's interface redesign!
Scientific notation, or E notation, is used to more simply represent values that are very large or very small. Rather than represent the vertical distance from the top of Mount Everest to the bottom of the Marianas Trench as 19795000 millimeters (why millimeters, you ask? Well, why not?), expressing this distance in scientific notation, 1.9795e+7 mm, provides a more accessible way to understand the magnitude and precision of that value. When databases and spreadsheets format data in scientific notation, that formatting may be carried over into Alteryx. For some users, data in scientific notation can be problematic, especially if the data type is read in Alteryx as a string. Some Alteryx users have posted their helpful ideas on dealing with converting data in scientific notation to the full numeric value, and the links to those discussion threads are provided below. This article summarizes and demonstrates their ideas.
Figure 1: Depending on its size and the selected field type, a number may maintain scientific notation formatting. Left: Files brought in as a .csv contain scientific notation as a string field type. Right: Sample data from an .xlsx file type contains scientific notation as a numeric field type (double).
Converting from String to Numeric Values:
To convert values stored as text from scientific notation to their full numeric value, values not only need to be re-formatted for visual purposes but also re-defined as numeric values. The easiest way to accomplish this is to use a Formula tool (or, in the event that you have many fields to convert, a Multi-Field Formula tool). The “ToNumber” expression, found under the function category “Conversions”, converts a value stored as text to a numeric data type (Figure 2). After applying this formula to the data, the full numeric values can be displayed.
Figure 2: Applying the Conversion function “ToNumber” changes what was previously a string field into a numeric field that displays the values denoted by scientific notation.
Displaying Numeric Values to Full Extent:
Even when values are read into Alteryx as numeric field types (usually as a Double), they may still appear in scientific notation. This generally happens when dealing with very large or very small numbers with a high exponent (E) value. Mathematically, these values are viable for use in numeric functions. However, for data visualization, it may be helpful to remove the scientific notation formatting. In the case of a large exponent value, changing the field type to Fixed Decimal is a good option for accommodating the full extent of a value (Figure 3). This, of course, has a couple of drawbacks. First, it requires a high degree of familiarity with the data to appropriately size the field’s scale and precision. Second, it removes some flexibility for visualizing results from mathematical processes. Take, for example, a value of 0.005 in a Fixed Decimal field (size 6.4). Multiplying this value by 0.001, the conversion factor to convert from millimeters to meters, would display as 0.0000 as the result since 0.000005 exceeds the field size settings. However, if a limited scale (values to the right of the decimal place) or manual control over the field size is more desirable than possibly losing some granularity of the data, then this method is a useful technique.
Figure 3: Setting a field type to Fixed Decimal requires a high familiarity with the data, but it ensures that the length of a value is fully displayed.
The original threads for this discussion can be found here and here.