on 05-23-2016 08:04 AM - edited on 09-05-2023 07:28 AM by RRosa
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.
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.
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.
The original threads for this discussion can be found hereand here.