**Contents:**

- If the source value does not include a valid input for this function, a missing value is returned.
When a

`NUMFORMAT`

transform 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 (e.g. Decimal to Integer).- For more information on formatting date values, see DATEFORMAT Function.

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.

**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(MyPrice, '$###,###.##')

**Output:** Returns the values from the `MyPrice`

column converted to a price format.

## Syntax and Arguments

numformat(numeric_val, number_format_string)

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 to apply |

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.

**
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

Some key codes:

Code | Description | Example Format String | Example Inputs | Example Outputs |
---|---|---|---|---|

# | Insert a digit if it is present in the data. |
| 99 999 1000 10000 | 99 999 1,000 10,000 |

0 | Insert a digit even if it is not present in a data. | `'00.##'` | 20 7.1 | 20 07.1 |

$ | You can add constants values to the expression. Whitespace is respected. For example, you can insert currency markers at the beginning of your expression. | `'$ ##.##'` | 20 2514.22 6.6666 | $ 20 $ 2514.22 $ 6.67 |

% | Percentage expressions can be at the back of the number formatting expression. | `'##.## %'` | 20 2514.22 6.6666 | 20 % 2514.22 % 6.67% |

Trifacta supports Java number formatting strings, with some exceptions.

Missing values for this function in the source data result in missing values in the output.

**
Usage Notes:
**

Required? | Data Type | Example Value |
---|---|---|

Yes | String | `'#.#'` |

## Examples

**Tip:** For additional examples, see Common Tasks.

### Example - formatting price and percentages

This example steps through how to manage number formatting for price and percentage data when you have to 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
`OrderId`

is an Integer, when it should be treated as String data. - The
`UnitPrice`

,`Discount`

, and`TaxRate`

columns 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 Trifacta pattern.

Transformation Name | `Replace text or pattern` |
---|---|

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 | `Replace text or pattern` |
---|---|

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 | `New formula` |
---|---|

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 | `New formula` |
---|---|

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 | `New formula` |
---|---|

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 | `Edit column with formula` |
---|---|

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 | `Edit column with formula` |
---|---|

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 |

This page has no comments.