| Excerpt |
|---|
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. |
Numeric literal example:
| D code |
|---|
derive type:single value: TRUNC(PI()) |
Output: Generates a column with each row's value 3.
Expression example:
| D code |
|---|
derive type:single value: TRUNC(length_in * length_in, 2) |
Output: Generates a column containing the square of the values in length_in, truncated to two decimal points.
| D code |
|---|
derive type:single value: TRUNC(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 truncate. - Default is
0, which truncates to the nearest integer. - Negative integer values can be applied.
|
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.
| 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.
| Required? | Data Type | Example Value |
|---|
| No | Integer literal | 3 |
Example - Basic TRUNC
Source:
| RowId | myVal |
|---|
| r01 | 1.2345 |
| r02 | -1.2345 |
| r03 | 100.000 |
| r04 | 10.1 |
| r05 | 50.029 |
Transform:
| D code |
|---|
derive type:single value:TRUNC(myVal) as: 'trunc_myVal' |
| D code |
|---|
derive type:single value:TRUNC(myVal,2) as: 'trunc_myVal2' |
| D code |
|---|
derive type:single value:TRUNC(myVal,2) as: 'trunc_myVal_2' |
Results:| RowId | myVal | trunc_myVal | trunc_myVal2 | trunc_myVal_2 |
|---|
| r01 | 1.2345 | 1 | 1.23 | 0 |
| r02 | -1.2345 | -1 | -1.23 | 0 |
| r03 | 100.000 | 100 | 100.00 | 100 |
| r04 | 10.1 | 10 | 10.1 | 0 |
| r05 | 50.029 | 50 | 50.02 | 0 |
Example - RANDBETWEEN, PI, and TRUNC functions
| Include Page |
|---|
| EXAMPLE - RANDBETWEEN and PI Functions |
|---|
| EXAMPLE - RANDBETWEEN and PI Functions |
|---|
|
