Supported Numeric Formatting
The following formatting can be applied to Integer and Decimal types or to String values that are being converted to numeric types.
Tip
Dataprep by Trifacta supports Java number formatting strings, with some exceptions.
Supported 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 | Indicate required digits. If a digit is not available in the source, inserts zero in the data. | '00.##' | 20 7.1 | 20.00 07.1 |
$ | You can add constants values to the expression. For example, you can insert currency markers at the beginning of your expression. Note The following currency formats are supported: $", "€", "£", "¥", "₩", "₹", "NT$", "R$", "R", "Rs", "Kr Whitespace is respected, except in the following case. | '$ ##.##' | 20 2514.22 6.6666 | $ 20 $ 2514.22 $ 6.67 |
(space) | You can use space as a grouping separator. When space is used to group sets of digits, all other whitespace in the value is trimmed. | '$ ###.##' where space is used as grouping separator. | 123456.78 £ 123456.78 | $123 456.78 $123 456.78 |
% | Percentage expressions can be at the back of the number formatting expression. Note When the percentage sign is added to the format string, the value is automatically multiplied by 100. When the format string is used with the NUMVALUE function, the value is automatically divided by 100 to return the decimal value. | '##.## %' | 0.20 14.22 6.6666 | 20 % 1422 % 666.67 % |
- | Negative value indicators can be added to the front part of the number formatting string.
Note After the formatting has been applied, type inference may be re-applied to the column, which can change the data type of the column. | '-###,###.00' | 123 -123 1234.56 -1234.56 | -123.00 --123.00 -1234.56 --1234.56 |
Key Codes as Separator Values
Some functions support the use of specifying key codes for grouping and decimal separators:
Note
Separators must be specified when using the NUMVALUE function.
Separators for locales
Grouping and decimal separators can be used to format values for specific locales. Below, you can see how you can format values for locales.
Example Locale | Grouping Separator | Decimal Separator | Example Formatting | Example Output |
|---|---|---|---|---|
U.S locale | Comma ( | Period ( | NUMFORMAT(SUM(1000000,DIVIDE(1,100)),'###,###.00',',','.') | 1,000,000.01 |
Spanish locale | Period ( | Comma ( | NUMFORMAT(SUM(1000000,DIVIDE(1,100)),'###,###.00','.',',') | 1.000.000,01 |
French locale | Space | Comma ( | NUMFORMAT(SUM(1000000,DIVIDE(1,100)),'###,###.00',' ',',') | 1 000 000,01 |
Example Separators
Input | Example Format String | Grouping Separator | Decimal Separator | Output |
|---|---|---|---|---|
123.45 | ##.00 | , | . | 123.45 |
123.4 | ##.00 | , | . | 123.40 |
1234 | #,### | , | . | 1,234 |
1234.5 | #,###.# | , | . | 1,234.5 |
1234.56 | #,###.## | , | . | 1,234.56 |
1234 | ###,# | . | , | 1.234 |
1234.56 | ###,# | . | , | 1.234,56 |
1234 | #,## | . | , | 1.234 |
1234 | #.###,0 | . | , | 1.234,0 |
123.45 | ##,# | space | , | 123,45 |
1234 | # ### | space | , | 1 234 |
1234.5 | # ###,# | space | , | 1 234,5 |
1234.56 | # ###,## | space | , | 1 234,56 |