Page tree

Trifacta SaaS



Contents:

   

Contents:


The following formatting can be applied to Integer and Decimal types or to String values that are being converted to numeric types. 

Tip: Trifacta SaaS supports Java number formatting strings, with some exceptions.

Supported Key Codes

CodeDescriptionExample Format StringExample InputsExample 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.

  • Negative value indicators at the end of the number are not supported.
  • If the source value is positive, the negative value is rendered.

    NOTE: In this case, the source value is formatted to appear as its opposite.

  • If the source value is negative, a second dash is added to the front of the value. See examples.

    NOTE: In this case, the value is formatted as a non-numeric value. You can add a second step to your recipe to remove the second dash from column values.

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.


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 LocaleGrouping SeparatorDecimal SeparatorExample FormattingExample Output
U.S localeComma (,)Period (.)
NUMFORMAT(SUM(1000000,DIVIDE(1,100)),'###,###.00',',','.')
1,000,000.01
Spanish localePeriod (.)Comma (,)
NUMFORMAT(SUM(1000000,DIVIDE(1,100)),'###,###.00','.',',')
1.000.000,01
French localeSpaceComma (,)
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

This page has no comments.