NUMFORMAT Function

...

Excerpt
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 a NUMFORMAT transform 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
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. For example, Decimal to Integer).

For more information on formatting date values, see DATEFORMAT Function

.

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.

Info

NOTE: If the function is unable to process the value, a null value is returned on

D s photon

. On other running environments, trailing characters that do not apply to numeric values or their formatting are simply dropped.

D s product

r	true

supports a wide variety of number formats, following Java standards. For more information, please see Java's documentation.

Info
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.

D s lang vs sql

D s

snippet	Basic

numformat

D lang syntax

RawWrangle	true
Type	ref
showNote	true
WrangleText	derive type:single value: numformat(MyPrice, '$###,###.##') as: 'dateformat_MyPrice'

, grouping separator, decimal separator))

Numformat(MyPrice, '$###,###.##$ ##,##.#', ',' ,'.')

Output: Returns the values from the MyPrice column converted to a price formatby 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.

D s

snippet	Syntax

D lang syntax

RawWrangle	true
Type	syntax
showNote	true
WrangleText	derive type:single value:numformat(numeric_col, number_format_string)

numformatNumformat(numeric_val, number_format_string, [grouping_separator], [decimal_separator])

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

D s lang notes

numeric_val

...

Values with more than 20 digits after the decimal point 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.

D s

snippet	usage

Required?	Data Type	Example Value
Yes	String (column reference), function, or Integer or Decimal literal	`MyPrice`

...

Info
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:

...

'###,###'

...

99

999

1000

10000

...

99

999

1,000

10,000

...

20

7.1

...

20

07.1

...

D s product

supports Java number formatting strings, with some exceptions.

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

D s

snippet	usage

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”).

Info
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.

Info
NOTE: Using invalid separators or wrong separators may generate errors in your recipe step.

D s

snippet	usage

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").

D s

snippet	usage

Required?	Data Type	Example Value

Yes

No String '

#

.

#

'

D s

snippet	Examples

D s product

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 have to must perform some computations on the data in the application.

...

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

D s item

pattern

item	pattern

.

D trans

RawWrangle	true
p03Value	''
Type	step
WrangleText	replace col: * on: `\%{end}` with: ''
p01Name	Columns
p01Value	All
p02Name	Find
p02Value	`\%{end}`
p03Name	Replace with
SearchTerm	Replace text or pattern

...

D trans

RawWrangle	true
Type	step
WrangleText	set col:SubTotal value:numformat(SubTotal, '#.00')
p01Name	Columns
p01Value	SubTotal
p02Name	Formula
p02Value	numformatNUMFORMAT(SubTotal, '#.00', ',' ,'.')
SearchTerm	Edit column with formula

...

The Discount and TaxRate values should be converted to decimalsDecimals. The following adjusts the Discount column:

...

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

...

See Also

...

D s also

label	math

Page tree

Versions Compared

Old Version 17

New Version Current

Key

numeric_val

grouping_separator

decimal_separator

Example - formatting price and percentages