NumberFormat Function

This function attempts to convert the input into a numerical figure.

If the conversion is successful, the numerical figure is formatted as text according to the given parameters, e.g. the pattern.

If the conversion is unsuccessful, the default value is returned if available. Input consists of one input field.

Note: the function returns a column of type "String". To obtain a column of a numerical data type, the Groovy function can be used.

Parameters

Pattern Pattern for the display of the input as a decimal figure.

These symbols of the pattern are set and unchangeable. Grouping and decimal separators for the output can be specified in the last parameters. Below are examples:

For a comprehensive list of symbols, see http://download.oracle.com/javase/6/docs/api/java/text/DecimalFormat.html

Default Result if the input cannot be converted into a numerical figure (optional). If no default is entered, the input remains unchanged.
ExponentialFormat Display of positive numerical figures in exponential form (optional). Possible alternatives:
  • ‘E+0’: Display in the form 123E+45
  • ‘E0’: Display in the form 123E45 (standard)
MoveSign

Shifts the minus sign one place (optional). Possible alternatives:

  • ‘left’: If the minus sign is on the right, it is shifted to the left
  • ‘right”: If the minus sign is on the left, it is shifted to the right
GroupingSeparator Grouping separator for the output
DecimalSeparator Decimal separator for the output

Note: numerical values that are exported from SAP systems are partly shown in the form "999-".

Make sure that the input values have a standard format, otherwise it may cause errors (see image below). In line 1, the comma is identified as decimal separator, in line 2 the point, again in line 3, the comma. The value in line 4 is not recognized as a number.

In such cases, you should replace the points with “nothing”. Thereafter, the values are passed uniformly in the column “Transformation”.

Updated December 9, 2024