Contents:
Basic Usage
Numeric literal example:
trunc(pi())
Output: Returns the value 3.
Expression example:
trunc(length_in * length_in, 2)
Output: Returns the square of the values in length_in, truncated to two decimal points.
Syntax and Arguments
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.
|
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 |
Tip: For additional examples, see Common Tasks.
Examples
Example - Basic TRUNC
Source:
| RowId | myVal |
|---|---|
| r01 | 1.2345 |
| r02 | -1.2345 |
| r03 | 100.000 |
| r04 | 10.1 |
| r05 | 50.029 |
Transformation:
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | trunc(myVal) |
| Parameter: New column name | 'trunc_myVal' |
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | trunc(myVal,2) |
| Parameter: New column name | 'trunc_myVal2' |
| Transformation Name | New formula |
|---|---|
| Parameter: Formula type | Single row formula |
| Parameter: Formula | trunc(myVal,2) |
| Parameter: New column name | '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
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' |
Results:
After the trunc_circumference_in column is dropped, the data should look similar to the following:
| prodId | radius_in | area_sq_in | circumference_in | testPt01_in | testPt02_in |
|---|---|---|---|---|---|
| p001 | 1 | 3.142 | 6.283 | 5 | 5 |
| p002 | 2 | 12.566 | 12.566 | 3 | 3 |
| p003 | 3 | 28.274 | 18.850 | 13 | 13 |
| p004 | 4 | 50.265 | 25.133 | 24 | 24 |
| p005 | 5 | 78.540 | 31.416 | 0 | 0 |
| p006 | 6 | 113.097 | 37.699 | 15 | 15 |
| p007 | 7 | 153.938 | 43.982 | 11 | 11 |
| p008 | 8 | 201.062 | 50.265 | 1 | 1 |
| p009 | 9 | 254.469 | 56.549 | 29 | 29 |
| p010 | 10 | 314.159 | 62.832 | 21 | 21 |
This page has no comments.