5.4.2 - Rescale - Teradata Warehouse Miner

In-Database Analytic Functions User Guide

Teradata Warehouse Miner
October 2016
User Guide


Rescaling limits the upper and/or lower boundaries of the data in a continuous numeric column using a linear rescaling function based on maximum and/or minimum data values. It may be useful with algorithms that require or work better with data within a certain range. Rescale is only valid on numeric columns, and not columns of type date.

You can supply new minimum and maximum values (lower, upper) to form new variable boundaries. If only the lower boundary is supplied, the variable is aligned to this value; or if only an upper boundary value is specified, the variable is aligned to that value. If a requested column has a constant value (max and min are the same), then the transformation will fail with an SQL error.

The rescale transformation formulas are shown in the following examples. The l denotes the left bound and not the numeral 1, while r denotes the right bound.

When both the lower and upper bounds are specified:

f(x,l,r) = (l+(x-min(x))(r-l))/(max(x)-min(x))

When only the lower bound is specified:

F(x,l) = x-min(x)+l

When only the upper bound is specified:

f(x,r) = x-max(x)+r

Rescaling supports only numeric type columns.


call twm. td_analyze('vartran','database=twm_source;tablename=twm_customer;General Parameters;rescale={rescalebounds (lowerbound/0),columns (colvalues)};');

Required Parameters

Controls the name of the output (transformed) column and its data type. The columns parameter is required by all transformations except Derive. A separate transformation is performed for each column in the list. If a column name is followed by a forward slash and a name, the name after the slash becomes the name of the transformed column in the resultant output table. Otherwise the column name is used as the output column name.
For the Derive transformation, the outputname parameter controls the naming of the transformed output column.
The database containing the input table.
The parameter that identifies the type of transformation being performed.
The rescalebounds parameter can take one of the following forms. A slash is used to separate the keywords lowerbound and upperbound from the designated values. The third example is the default.
  • rescalebounds (lowerbound/0)
  • rescalebounds (upperbound/1)
  • rescalebounds (lowerbound/0,upperbound/1)
The name of the table to be transformed.
This parameter is required to run a variable transformation. The vartran parameter is always enclosed in single quotes.

General Parameters (separated by a semi-colon)

For all transformation types, the datatype parameter is used to cast the column to a desired database data type provided it is compatible with the transformed data.
The allowed output types include:
  • byteint
  • char
  • date
  • decimal
  • float
  • integer
  • smallint
  • time
  • timestamp
  • varchar
  • bigint
  • number
When set to true, this parameter requests a mirrored copy of the output table in the Teradata Database when outputstyle=table.
When set to true, the SQL for the requested transformations is returned as a result set but not executed. When this parameter is not specified or is set to false, the SQL is executed but not returned.
When set to true, requests the output table contain the index columns when outputstyle=table.
When set to true, requests the output table contain a unique primary index when outputstyle=table.
When null replacement is requested, either via a Null Replacement transformation or in combination with a Bin Code, Derive, Design Code, Recode, Rescale, Sigmoid or Z Score transformation, the keycolumns parameter must be specified. The column or columns listed must form a unique key into the input and output table of the transformation.

Requests the generated SQL contain the given locking clause in the appropriate location depending on the output style.

An example of a locking clause when the output style defaults to select is:

LOCKING mydb.mytable FOR ACCESS;

When set to true, requests an output table that may contain duplicate rows when outputstyle=table.
When set to true, requests the output table contain no index columns when outputstyle=table.
Data types supported by various nullstyle parameters are:
Data Type Description Example
literal,value numeric, character, and date nullstyle (literal,value)
mean numeric and date nullstyle (mean)
median numeric and date nullstyle (median)
medianwithoutaveraging any supported data type nullstyle (medianwithoutaveraging)
mode any supported date type nullstyle (mode)
imputed,table any supported data type nullstyle (imputed,tablename)

If date values are entered, the keyword DATE must precede the date value, which should not be enclosed in single quotes.

The database that will contain the resulting output table when outputstyle=table or view.
The allowed output styles are:
  • select
  • table
  • view
If outputstyle is not specified, the function generates a SELECT statement and does not create a table or view.
The name of the output table when outputstyle=table or view.
Requests the generated SQL contain the given WHERE clause in appropriate places in the generated SQL. This is independent of the output style requested.


Examples in this section show how to use Rescale. These examples assume that the td_analyze function has been installed in a database named twm.

The following example demonstrates the Rescale transformation.

call twm.td_analyze('vartran','database=twm_source;tablename=twm_customer;rescale={rescalebounds(lowerbound/0,upperbound/1),columns(income/inc,age)}{rescalebounds(upperbound/1),columns(income/income1,age/age1)}{rescalebounds(lowerbound/0),columns(income/income2,age/age2)};');

The following example demonstrates combined null replacement. keycolumns must be included as a general parameter when null value replacement is performed.

call twm.td_analyze('vartran','database=twm_source;tablename=twm_customer;keycolumns=cust_id;rescale=rescalebounds(lowerbound/0,upperbound/1),nullstyle(literal,0),columns(age,income);');