NUMFORMAT Function
Formats a numeric set of values according to the specified number formatting. Source values can be a literal numeric value, a function returning a numeric value, or reference to a column containing an Integer or Decimal values.
If the source value does not include a valid input for this function, a missing value is returned.
When this function is applied, the column can be re-typed to a different data type. For example, if your format string (second parameter) is
'#'(a single hash mark), then all values are rounded to the nearest integer, and the column is re-typed as Integer.Tip
In general, you should format your numeric data after you have completed your computations on it. In some cases, you might lose numeric precision in converting formats, or your data can be re-typed to a different data type (For example,Decimalto Integer).
You can also use decimal separators and grouping separators when working with data from multiple locales. If no separators are provided, the U.S. format separators are used.
When this function is applied to any column, the resulting column is of String type, so arithmetic operations are not possible on the resulting column.
Note
If the function is unable to process the value, a null value is returned on Trifacta Photon. On other running environments, trailing characters that do not apply to numeric values or their formatting are simply dropped.
Dataprep by Trifacta supports a wide variety of number formats, following Java standards. For more information, please see Java's documentation.
Note
This function just changes how the underlying cell value is displayed. If you round the value to a specific level of precision, please use the ROUND function. See ROUND Function.
For more information on formatting date values, see DATEFORMAT Function.
Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.
Basic Usage
Numformat(<code>MyPrice</code>, '<code><span class="author-d-1gg9uz65z1iz85zgdz68zmqkz84zo2qowz82zfhz88zz69zyz82z4z74z55g8rhz76zh7z67zk5z79zg5u1s77pz89z h-ldquo">$ </span><span class="author-d-1gg9uz65z1iz85zgdz68zmqkz84zo2qowz82zfhz88zz69zyz82z4z74z55g8rhz76zh7z67zk5z79zg5u1s77pz89z">##,##.#</span></code>', '<code>,</code>' ,'<code>.</code>')
Output: Returns the values from the MyPrice column by formatting the values using the specified formatting string and group and separators separators. For example, if the MyPrice column has a value of 12345.12 then it can be reformatted to $1,23,45.12 by using the above parameters.
Syntax and Arguments
<code class="listtype-code listindent1 list-code1 lang-bash"><span>Numformat</span>(<span>numeric_val</span>, <span>number_format_string</span>, [grouping_separator], [decimal_separator])</code>
Argument | Required? | Data Type | Description |
|---|---|---|---|
numeric_val | Y | string, integer, or decimal | Literal value, function returning a numeric value, or name of Integer or Decimal column whose values are to be formatted |
number_format_string | Y | string | Literal value of the number formatting string used to indicate location of separators, number of required digits, currency, percentage, and sign. |
grouping_separator | N | string | A grouping representing grouping separator. By default, comma (,) is used as the grouping separator. |
decimal_separator | N | string | A string representing decimal separator. By default, period (.) is used as the decimal separator. |
For more information on syntax standards, see Language Documentation Syntax Notes.
numeric_val
Literal numeric value, a function that returns a numeric value, or the name of the column whose Integer or Decimal data is to be formatted.
Values with more than 20 digits after the Decimal point are truncated by this function.
Missing values for this function in the source data result in missing values in the output.
Multiple columns and wildcards are not supported.
Using a dash as a negative value indicator (e.g. '-###.00') in your formatting string can change values and their data types.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
Yes | String (column reference), function, or Integer or Decimal literal | MyPrice |
number_format_string
String value indicating the number format to apply to the input values.
Note
You cannot create number format strings in which a 0 value appears before a # value. The following example strings are not supported: #.#0, #.#0#, #.#00
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
Yes | String | '###.00' |
grouping_separator
The string used to separate a group of digits. 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”).
Note
If a space is used as the grouping separator, then any space values between a currency indicator and digits are automatically trimmed. A grouping separator should not be inserted between a currency indicator and a digit.
Note
Using invalid separators or wrong separators may generate errors in your recipe step.
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
No | String | ',' |
decimal_separator
The string used to separate the integer part of a Decimal value from its fractional part. 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").
Usage Notes:
Required? | Data Type | Example Value |
|---|---|---|
No | String | '.' |
Examples
Tip
For additional examples, see Common Tasks.
Dataprep by Trifacta supports Java number formatting strings, with some exceptions.
Example - formatting price and percentages
This example steps through how to manage number formatting for price and percentage data when you must perform some computations on the data in the application.
Source:
In this case, you need to compute sub-total and totals columns.
OrderId | Qty | UnitPrice | Discount | TaxRate |
|---|---|---|---|---|
1001 | 5 | $25.00 | 0% | 8.25% |
1002 | 15 | $39.99 | 5% | 8.25% |
1003 | 2 | $99.99 | 15% | 8.25% |
1004 | 100 | $999.99 | 0% | 8.25% |
Transformation:
When this data is first imported into the Transformer page, you might notice the following:
The data type for
OrderIdis an Integer, when it should be treated as String data.The
UnitPrice,Discount, andTaxRatecolumns are typed as String data because of the unit characters in the values.
Note
Where possible, remove currency and three-digit separators from your numeric data prior to import.
You can re-type the OrderId column to String without issue. If you retype the other three columns, all values are mismatched. You can use the following transforms to remove the currency and percentage notation. The first transform removes the trailing % sign from every value across all columns using a Alteryx pattern.
Transformation Name |
|
|---|---|
Parameter: Columns | All |
Parameter: Find | `\%{end}` |
Parameter: Replace with | '' |
You can use a similar one to remove the $ sign at the beginning of values:
Transformation Name |
|
|---|---|
Parameter: Columns | All |
Parameter: Find | `{start}\$` |
Parameter: Replace with | '' |
When both are applied, you can see that the data types of each column is updated to a numeric type: Integer or Decimal. Now, you can perform the following computations:
Transformation Name |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | (Qty * UnitPrice) |
Parameter: New column name | 'SubTotal' |
You can use the new SubTotal column as the basis for computing the DiscountedTotal column, which factors in discounts:
Transformation Name |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | (SubTotal - (SubTotal * (Discount / 100))) |
Parameter: New column name | 'DiscountedTotal' |
The Total column applies the tax to the DiscountedTotal column:
Transformation Name |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | DiscountedTotal * (1 + (TaxRate / 100)) |
Parameter: New column name | 'Total' |
Because of the math operations that have been applied to the original data, your values might no longer look like dollar information. You can now apply price formatting to your columns. The following changes the number format for the SubTotal column:
Transformation Name |
|
|---|---|
Parameter: Columns | SubTotal |
Parameter: Formula | NUMFORMAT(SubTotal, '#.00', ',' ,'.') |
Note that the leading $ was not added back to the data, which changes the data type to String. You can apply this transform to the Price, DiscountedTotal, and Total columns.
Note
The data types for your columns should match the expected inputs for your downstream analytics system.
The Discount and TaxRate values should be converted to Decimals. The following adjusts the Discount column:
Transformation Name |
|
|---|---|
Parameter: Columns | Discount |
Parameter: Formula | (Discount / 100) |
Results:
The output data should look like the following:
OrderId | Qty | UnitPrice | SubTotal | Discount | DiscountedTotal | TaxRate | Total |
|---|---|---|---|---|---|---|---|
1001 | 5 | 25.00 | 125.00 | 0 | 125.00 | 0.0825 | 135.31 |
1002 | 15 | 39.99 | 599.85 | 0.05 | 569.86 | 0.0825 | 616.87 |
1003 | 2 | 99.99 | 199.98 | 0.15 | 169.98 | 0.0825 | 184.01 |
1004 | 100 | 999.99 | 99999.00 | 0 | 99999.00 | 0.0825 | 108248.92 |