ROUND Function

Rounds input value to the nearest integer. Input can be an Integer, a Decimal, a column reference, or an expression. Optional second argument can be used to specify the number of digits to which to round.

When rounding to nearest integer, decimal values that are X.5 or more are rounded to X+1.

Note

This function changes the actual data of the value. If you just want to change how the data is formatted for display, please use the NUMFORMAT function. See NUMFORMAT Function.

Wrangle vs. SQL: This function is part of Wrangle, a proprietary data transformation language. Wrangle is not SQL. For more information, see Wrangle Language.

Basic Usage

Numeric literal example:

round(2.5)

Output: Rounds the input value to the nearest integer: 3.

Expression example:

round(MyValue + 2.5)

Output: Rounds to the nearest integer the sum of 2.5 and the value in the MyValue column.

Numeric literal example:

round(pi(),4)

Output: Rounds pi to four decimal points: 3.1416.

Syntax and Arguments

round(numeric_value, integer_value)

Argument	Required?	Data Type	Description
numeric_value	Y	string, decimal, or integer	Name of column or Decimal or Integer literal to apply to the function
integer_value	N	integer	Number of digits to which to round. Default is `0`, which rounds to the nearest integer. Negative integer values can be applied.

For more information on syntax standards, see Language Documentation Syntax Notes.

numeric_value

Name of the column, numeric literal, or numeric expression.

Missing input values generate missing results.
Literal numeric values should not be quoted. Quoted values are treated as strings.
Multiple columns and wildcards are not supported.

Usage Notes:

Required?	Data Type	Example Value
Yes	String (column reference) or Integer or Decimal literal	2.5

integer_value

Number of digits to which to round the first argument of the function.

Positive values values truncate to the right of the decimal point.
Negative values truncate to the left of the decimal point.
Missing input values generate missing results.

Usage Notes:

Required?	Data Type	Example Value
No	Integer literal	3

Examples

Tip

For additional examples, see Common Tasks.

Example - Exponential functions

The following example demonstrates how the rounding functions work together. These functions include the following:

FLOOR - largest integer that is not greater than the input value. See FLOOR Function.
CEILING - smallest integer that is not less than the input value. See CEILING Function.
ROUND - nearest integer to the input value. See ROUND Function.
MOD - remainder integer when input1 is divided by input2. See Numeric Operators.

Source:

rowNum	X
1	-2.5
2	-1.2
3	0
4	1
5	1.5
6	2.5
7	3.9
8	4
9	4.1
10	11

Transformation:

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	FLOOR(X)
Parameter: New column name	'floorX'

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	CEILING(X)
Parameter: New column name	'ceilingX'

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	ROUND (X)
Parameter: New column name	'roundX'

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	(X % 2)
Parameter: New column name	'modX'

Results:

rowNum	X	modX	roundX	ceilingX	floorX
1	-2.5		-2	-2	-3
2	-1.2		-1	-1	-2
3	0	0	0	0	0
4	1	1	1	1	1
5	1.5		2	2	1
6	2.5		3	3	2
7	3.9		4	4	3
8	4	0	4	4	4
9	4.1		4	5	4
10	11	1	11	11	11

Example - RANDBETWEEN and PI and ROUND functions

This example illustrates how you can apply the following functions to generate new and random data in your dataset:

RANDBETWEEN - Generate a random Integer value between two specified Integers. See RANDBETWEEN Function.
PI - Generate the value of pi to 15 decimal points. See PI Function.
ROUND - Round a decimal value to the nearest Integer or to a specified number of digits. See ROUND Function.
TRUNC - Round a value down to the nearest Integer value. See TRUNC Function.

Source:

In the following example, a company produces 10 circular parts, the size of which is measured in each product's radius in inches.

prodId	radius_in
p001	1
p002	2
p003	3
p004	4
p005	5
p006	6
p007	7
p008	8
p009	9
p010	10

Based on the above data, the company wants to generate some additional sizing information for these circular parts, including the generation of two points along each part's circumference where quality stress tests can be applied.

Transformation:

To begin, you can use the following steps to generate the area and circumference for each product, rounded to three decimal points:

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	ROUND(PI() * (POW(radius_in, 2)), 3)
Parameter: New column name	'area_sqin'

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	ROUND(PI() * (2 * radius_in), 3)
Parameter: New column name	'circumference_in'

For quality purposes, the company needs two tests points along the circumference, which are generated by calculating two separate random locations along the circumference. Since the RANDBETWEEN function only calculates using Integer values, you must first truncate the values from circumference_in:

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	TRUNC(circumference_in)
Parameter: New column name	'trunc_circumference_in'

Then, you can calculate the random points using the following:

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	RANDBETWEEN(0, trunc_circumference_in)
Parameter: New column name	'testPt01_in'

Transformation Name	`New formula`
Parameter: Formula type	Single row formula
Parameter: Formula	RANDBETWEEN(0, trunc_circumference_in)
Parameter: New column name	'testPt02_in'