| Excerpt |
|---|
Converts a string formatted as a number into an Integer or Decimal value by parsing out the specified decimal and group separators. A string or a function returning formatted numbers of String type or a column containing formatted numbers of string type can be inputs. |
You can use this function to convert String values that have locale-specific formatting to locale-independent values of Integer or Decimal type by removing the formatting separators.
- If the source value does not include a valid input for this function, a missing value is returned.
- This function supports negative input values.
- Supports input for multiple locale types and returns the output of the appropriate numeric type.
You can use decimal separators and grouping separators when working with other currency formats. More information is below.
| Info |
|---|
NOTE: If the decimal separator and group separator arguments are not specified, then a null value is returned. |
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | ref |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value: numformat(MyPrice, '$###,###.##', grouping separator, decimal separator)) |
|---|
|
Numvalue(Discount, "," ,".") |
Output: Returns the values from the Discount column by converting the formatted number to a numeric value using decimal and group separators. For example, if the Discount column has values in percentage, then it is converted into the corresponding numeric value. Example: 10% to 0.10.
| Info |
|---|
NOTE: If multiple percent signs are used in a column, then this function returns the decimal values accordingly. For example, an input of 9%% returns 0.0009. |
| Info |
|---|
NOTE: This function removes any currency indicators. For example, £ 100 as an input value returns 100. |
| D lang syntax |
|---|
| RawWrangle | true |
|---|
| Type | syntax |
|---|
| showNote | true |
|---|
| WrangleText | derive type:single value:numformat(numeric_col, number_format_string) |
|---|
|
Numvalue(formattedNumber, ["grouping_separator"], ["decimal_separator"])
|
| Argument | Required? | Data Type | Description |
|---|
| formattedNumber | Y | string | |
| grouping_separator | Y | string | A grouping representing grouping separator. By default, comma (,) is used as the grouping separator.
|
| decimal_separator | Y | string | A string used to separate the integer and fractional part of the result. If not specified, then a null value is returned. |
Literal string value, a function that returns a string value, or the name of a column containing string values to be converted into a numeric value.
- Multiple columns and wildcards are not supported.
| Required? | Data Type | Example Value |
|---|
| Yes | | Discount |
grouping_separator
The string used to group a set of digits in the input values. Separators must be enclosed with double quotes (") or single quotes ('). For example, a comma ( , ) is used as a grouping separator in the U.S.A. (“10,000”), whereas space is used in France (“10 000”).
| Info |
|---|
NOTE: When you provide invalid separators or wrong separators, you may get an error in the Formula column. |
| Required? | Data Type | Example Value |
|---|
| Yes | | ',' |
decimal_separator
The string used to indicate the decimal point in the input values. Separators must be enclosed with double quotes ( " ) or single quotes ( ' ).
A decimal separator is used to separate the fractional part of a number written in decimal form. For example, a period (.) is used as a decimal separator in the U.S.A. ("1234.12"), whereas comma (,) is used in France ("1234,12").
| Required? | Data Type | Example Value |
|---|
| Yes | | '.' |
| Grouping Separator | Decimal Separator | Example Locale |
|---|
Comma (,) | Period (.) | U.S locale |
Period (.) | Comma (,) | Spanish locale |
| Space | Comma (,) | French locale |
This example shows how to convert the formatted number of string type into a numeric value. The following table shows different types of products and their total sales in the UK region. From this example, you must convert the total sales value for the U.S region.
First, you must convert the global currency formats into a generic numeric value, then proceed with the conversion calculation.
Source:
| Products | Total_Sales_UK |
|---|
Baby Foods | £ 100.00 |
Medicines | £ 150.00 |
Groceries | £ 200.00 |
Kitchen Supplies | £ 25.00 |
Cosmetics | £ 250.00 |
Snacks | £ 50.00 |
Transformation:
The first transformation is to convert the Total_Sales_UK column into a numeric value. In this case, when a Numvalue function is applied, the value with the currency symbols returns only the numeric value. You can see the currency symbol is removed when using the Numvalue function.
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: NUMVALUE (Total_Sales_UK, ",",".") |
|---|
| p01Name | columns |
|---|
| p01Value | Total_Sales_UK |
|---|
| p02Name | Formula |
|---|
| p02Value | NUMVALUE (Total_Sales_UK, ",",".") |
|---|
| p03Name | New column name |
|---|
| SearchTerm | Edit column with formula |
|---|
|
The second step consists of converting the U.K Pounds (£) to U.S Dollars ($) for better computations. In this step, you multiply the Total_Sales_UK column with the ratio conversion rate. Let's say the current conversion rate is 1.36, then multiply the U.K Pounds with 1.36 to convert to U.S Dollars. As a part of this transformation, the Total_Sales_US column is created.
| D trans |
|---|
| RawWrangle | true |
|---|
| p03Value | Total_Sales_US |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: NUMVALUE |
|---|
| p01Name | Formula type |
|---|
| p01Value | Single row formula |
|---|
| p02Name | Formula |
|---|
| p02Value | Total_Sales_UK * 1.36 |
|---|
| p03Name | New column name |
|---|
| SearchTerm | New formula |
|---|
|
After you get the U.S conversion, you can format the values using the Numformat function. The Numformat function formats a numeric set of values according to the specified number formatting.
| D trans |
|---|
| RawWrangle | true |
|---|
| Type | step |
|---|
| WrangleText | derive type:single value: NUMVALUE |
|---|
| p01Name | columns |
|---|
| p01Value | Total_Sales_US |
|---|
| p02Name | Formula |
|---|
| p02Value | NUMFORMAT(Total_Sales_US, '$###,###.00', ',', '.') |
|---|
| SearchTerm | Edit column with formula |
|---|
|
Results
The output data should look like the following:
| Products | Total_Sales_UK | Total_Sales_US |
|---|
Baby Foods | £ 100.00 | $136.00 |
Medicines | £ 150.00 | $204.00 |
Groceries | £ 200 .00 | $272.00 |
Kitchen Supplies | £ 25 .00 | $34. 00 |
Cosmetics | £ 250 .00 | $340. 00 |
Snacks | £ 50 .00 | $68. 00 |
