CloudCheckr FinanceManager Transform Statement: round

The `round` statement is used to ensure that numeric values in a column are whole multiples of a specified number.

Syntax

`round`_`colName [direction]``[to nearest``value]`

Details

The `round` statement is used to round numbers to the nearest multiple of any integer or floating point number.

The parameters supported by `round` are as follows:

 Parameter Default Purpose colName n/a The name of the column containing the values to round direction `up` Whether to round to the next highest (`up` ) or lowest ( `down` ) multiple of value value `1` A value determining the granularity of the rounding

The simplest form of the statement is `round colName`. This will use the defaults shown in the table above such that values are rounded to the next highest integer. Alternatively, the statement `round colName down` will round down to the nearest integer.

If the value argument is provided then the numbers in colName will be rounded up or down to the nearest multiple of value. For example the statement …

`round Quantity up to nearest 5`

… will round the numbers in the column Quantity up to the next multiple of 5. Thus any number in Quantity higher than 10 and less than 15 will be rounded to 15.

When specifying the value argument, floating point values are supported.

The `round` statement observes the following rules:

• Non-numeric values in colName are ignored
• Blank values, or a value of `0` in colName are ignored
• Numbers in colName that are already a whole multiple of value will not be modified

The `round` statement may be used in the body of a where statement to perform conditional rounding.

Examples

```round Quantity                       # Round up to nearest integer
round Quantity down                  # Round down to nearest integer
round Quantity up to nearest 4       # Round up to next multiple of 4
round Quantity down to nearest 2     # Round down to next lowest even number
round Quantity up to nearest 0.5     # ie: 2.25 -> 2.5
round Quantity down to nearest 0.1   # ie: 23.34567 -> 23.3
round Quantity down to nearest 0.01  # ie: 23.34567 -> 23.34
where ( ([quantity] > 5) && ([quantity] <= 100) ) {
round quantity up to nearest 10       # Force consumption in blocks of 10
}
where ([quantity] > 100) {
round quantity up to nearest 5     # Better deals for larger consumption
}
```