calculate

Overview

The calculate statement is used to perform arithmetic operations using literal and column values.

Syntax

calculate columnResultColas source operation source

where source is either of columncolName or valueliteral_value

and operation is one of the characters + - * / % for addition, subtraction, multiplication, division and modulo respectively.

There must be whitespace on each side of the _operation`_character

Examples: calculate column ResultCol as column Amount * value 1.2 calculate column Net as column total - column cogs calculate column constant_7 as value 3.5 + value 3.5

Details

The ResultCol parameter is the name of the column that will hold the results. This column may or may not exist (if necessary it will be created automatically).

Both of the two source parameters can specify a literal value, or the name of a column containing the value to use when performing the calculation.

• A literal value is specified using valueN where N is the literal number required

• A column name is specified using columncolName where ColName is the name of the column containing the values required

The ResultCol may be the same as a column specified by one of the source parameters in which case any existing values in it will be updated with the result of the calculation.

Additional notes:

• Any blank or non-numeric values in a source column will be treated as 0

• An attempt to divide by zero will result in 0

• When performing a modulo operation, the two source values are rounded to the nearest integer first

• If the result column already exists then if option overwrite is set to no, only blank cells in the result column will be updated.

Examples

• Add 1.5 to the values in the Rate column:

calculate column Rate as column Rate + value 1.5

• Multiply the values in the Rate column by those in the Quantity column

• Store the result in a new column called Charge

calculate column Charge as column Rate * column Quantity