Format number (formatnumber)

[ 1 Description ] [ 2 How does it work in the search window? ] [ 3 How does it work in LINQ? ]

Description

Converts a number according to a format and an optional locale. It is used to separate the digits following the specified format. The result will be a new column containing the strings formatted as specified, depending on the locale (if set).

How does it work in the search window?

Select Create field in the search window toolbar, then select the Format number operation. The conversion is the result of formatting the number specified in the Number argument, using the format specified in the Format, like #,###.0 argument, and applying the rules of the locale indicated in the third optional argument Locale (es, en-gb...).

Argument	Data type

Argument	Data type
Number ^mandatory	integer, float
Format, like #,###.0 ^mandatory	string
Locale (es, en-gb...) ^optional	string

The Format, like #,###.0 argument accepts the following patterns:

0	digit, zero padding (if the number is too short, zeros -in the locale's numeric set- will be added)
#	digit, no zero padding (if the number is too short, there is no need of adding zeros)
.	decimal separator or monetary decimal separator
-	negative sign
,	grouping separator
%	multiply by 100 and show as a percentage

Some Locale (es, en-gb...) examples are:

es, es_ES
en, en_US, en_GB, en_AU
fr, fr_FR

The data type of the values in the new field is string.

Example

In the demo.ecommerce.data table, we want to transform the format of the values in the bytesTransferred field to #,###. In addition, we want to consider the Spanish (Spain) locale (in Spanish, a period is used as the standard thousand separator). To do this, we will create a new field using the Format number operation.

The arguments needed to create the new field are:

Number - bytesTransferred field
Format, like #,###.0 - Click the pencil icon and enter #,###
Locale - Click the pencil icon and enter es_ES

Click Create field and you will see the following result:

How does it work in LINQ?

Use the operator select... as... and add the operation syntax to create the new column. These are the valid formats for the Format number operation:

formatnumber(number, string_format)
formatnumber(number, string_format, string_locale)

Example

You can copy the following LINQ script and try the above example on the demo.ecommerce.data table.

from demo.ecommerce.data
select formatnumber(bytesTransferred, "#,###", "es_ES") as bytesTransferred_formatted