EXAMPLE - RANDBETWEEN and PI Functions
This example illustrates how you can apply functions to generate random numeric data in your dataset.
Functions:
Item | Description |
|---|---|
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 |
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.
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 |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | ROUND(PI() * (POW(radius_in, 2)), 3) |
Parameter: New column name | 'area_sqin' |
Transformation Name |
|
|---|---|
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 |
|
|---|---|
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 |
|
|---|---|
Parameter: Formula type | Single row formula |
Parameter: Formula | RANDBETWEEN(0, trunc_circumference_in) |
Parameter: New column name | 'testPt01_in' |
Transformation Name |
|
|---|---|
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 |