Description
The Derive transformation requires the free-form transformation be specified
as a formula using the following operators, arguments, and functions:
+, -, **, *, /, %, (, ), x, y, z, abs, exp, ln, log, sqrt
The arguments x, y, and z can only assume the value of an input column.
An implied multiply operator is automatically inserted when a number,
argument (x, y, z), or parenthesis is immediately followed by an argument or
parenthesis.
For example,
4x means 4x, xy means xy, and x(x+1) is equivalent to x*(x+1).
An example formula for the quadratic equation is below.
formula="(-y+sqrt(y**2-4xz))/(2x)"
Note:
Output of this function is passed to "derive" argument of
td_transform_valib()
.
Usage
tdDerive(formula, columns, out.column, datatype=NULL, fillna=NULL)
Arguments
formula |
Required Argument. +, -, **, *, /, %, (, ), x, y, z, abs, exp, ln, log, sqrt Types: character | ||||||||||||||||||||||||||||||||||||
columns |
Required Argument. | ||||||||||||||||||||||||||||||||||||
out.column |
Required Argument. | ||||||||||||||||||||||||||||||||||||
datatype |
Optional Argument.
Notes:
Examples:
Types: character | ||||||||||||||||||||||||||||||||||||
fillna |
Optional Argument.
Types: tdFillNa |
Value
An object of tdDerive class.
Examples
Notes:
# 1. To run any transformation, user needs to use td_transform_valib()
# function.
# 2. To do so set option 'val.install.location' to the database name
# where Vantage analytic library functions are installed.
# 3. Datasets used in these examples can be loaded using Vantage Analytic
# Library installer.
# Get the current context/connection
con <- td_get_context()$connection
# Set the option 'val.install.location'.
options(val.install.location = "SYSLIB")
# Create object(s) of class "tbl_teradata".
sales <- tbl(con, "sales")
sales
# Example: Includes multiple derive transformations
# Derive transformation 1 is done with 3 variables, x, y, z, to
# calculate the total sales for the first quarter for each account.
# with td_transform_valib() function, when null
# replacement is being done.
# Create tdFillNa object.
fn_1 <- tdFillNa(style="literal", value=0)
# Create tdDerive object.
dr_1 <- tdDerive(formula="x+y+z", columns=c("Jan", "Feb", "Mar"),
out.column="q1_sales", fillna=fn_1)
# Derive transformation 2 is done with 2 variables, x, y, to calculate
# the sale growth from the month of Jan to Feb.
fn_2 <- tdFillNa(style='median')
dr_2 <- tdDerive(formula="((y-x)/x)*100", columns=c("Jan", "Feb"),
out.column="feb_growth", fillna=fn_2, datatype='bigint')
# Perform the derive transformation using td_transform_valib() function.
obj <- td_transform_valib(data=sales, derive=c(dr_1, dr_2),
key.columns="accounts")
obj$result