Page tree


Contents:

Our documentation site is moving!

For up-to-date documentation of Dataprep, please visit us at https://help.alteryx.com/Dataprep/.

   


Contents:


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)


ArgumentRequired?Data TypeDescription
numeric_valueYstring, decimal, or integerName of column or Decimal or Integer literal to apply to the function
integer_valueNinteger

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 TypeExample Value
YesString (column reference) or Integer or Decimal literal2.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
NoInteger literal3


Examples


Tip: For additional examples, see Common Tasks.

Example - Exponential functions

This example demonstrates how the rounding functions work together.

Functions:

ItemDescription
FLOOR Function Computes the largest integer that is not more than the input value. Input can be an Integer, a Decimal, a column reference, or an expression. 
CEILING Function Computes the ceiling of a value, which is the smallest integer that is greater than the input value. Input can be an Integer, a Decimal, a column reference, or an expression.
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.
MOD Function Returns the modulo value, which is the remainder of dividing the first argument by the second argument. Equivalent to the % operator.

Source:

rowNumX
1-2.5
2-1.2
30
41
51.5
62.5
73.9
84
94.1
1011

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:

rowNumXmodXroundXceilingXfloorX
1-2.5 -2-2-3
2-1.2 -1-1-2
300000
411111
51.5 221
62.5 332
73.9 443
840444
94.1 454
10111111111

See Also for EXAMPLE - Rounding Functions:

Example - RANDBETWEEN and PI and ROUND functions

This example illustrates how you can apply functions to generate random numeric data in your dataset.

Functions:

ItemDescription
RANDBETWEEN Function Generates a random integer between a low and a high number. Two inputs may be Integer or Decimal types, functions returning these types, or column references.
PI Function The PI function generates the value of pi to 15 decimal places: 3.1415926535897932.
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.
TRUNC Function Removes all digits to the right of the decimal point for any value. Optionally, you can specify the number of digits to which to round. Input can be an Integer, a Decimal, a column reference, or an expression. 
POW Function Computes the value of the first argument raised to the value of the second argument. 

Source:

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

prodIdradius_in
p0011
p0022
p0033
p0044
p0055
p0066
p0077
p0088
p0099
p01010

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'

Results:

After the trunc_circumference_in column is dropped, the data should look similar to the following:

prodIdradius_inarea_sq_incircumference_intestPt01_intestPt02_in
p00113.1426.28355
p002212.56612.56633
p003328.27418.8501313
p004450.26525.1332424
p005578.54031.41600
p0066113.09737.6991515
p0077153.93843.9821111
p0088201.06250.26511
p0099254.46956.5492929
p01010314.15962.8322121

See Also for EXAMPLE - RANDBETWEEN and PI Functions:

 

This page has no comments.