Derive Transform

Basic Usage

String literal example:

derive type: single value: 'passed' as:'status'

Output: Generates a new column called status, each row of which contains passed for its value. 

Column reference example:

derive type: single value:productName as:'orig_productName'

Output: Generates a new column called orig_productName, which contains all of the values in productName, effectively serving as a backup of the source column.

Function reference example:

derive type: single value:SQRT(POW(a,2) + POW(b,2)) as:'c'

Output: Generates a new column called c, which is the calculation of the Pythagorean theorem for the values stored in a and b. For more information on this example, see POW Function.

Window function example:

You can use window functions in your derive transforms:

derive type: multiple col: avgRolling value: ROLLINGAVERAGE(POS_Sales, 7, 0) group: saleDate order: saleDate

Output: Calculate the value in the column of avgRolling to be the rolling average of the POS_Sales values for the preceding seven days, grouped and ordered by the saleDate column. For more information, see Window Functions.

Syntax and Parameters

derive type: single|multiple value:(expression) [order: order_col] [group: group_col] [as:'new_column_name']

For more information on syntax standards, see Language Documentation Syntax Notes.

type

Type of formula in the transform:

Usage Notes:

value

Usage Notes:

order

This parameter specifies the column on which to sort the dataset before applying the specified function. For combination sort keys, you can add multiple comma-separated columns.

Usage Notes:

group

Identifies the column by which the dataset is grouped for purposes of applying the transform.

The ProdId column contains three values: P001, P002, and P003, and you add the following transform:

derive type: single value:SUM(Sales) group:ProdId as:'SalesByProd'

The above transform generates the SalesByProd column, which contains the sum of the Sales values, as grouped according to the three product identifiers.

If the value parameter contains aggregate or window functions, you can apply the group parameter to specify subsets of records across which the value computation is applied. 

You can specify one or more columns by which to group using comma-separated column references.

Usage Notes:

as

derive type: single value:(colX * colY) as:'areaXY'

Output: Generates a new column containing the product of the values in columns colX and colY. New column is explicitly named, areaXY.

Usage Notes:

Examples

Example - Basic Derive Examples

The following dataset is used for performing some simple statistical analysis using the derive transform.

Source:

Transform:

First, you can calculate the total average score across all tests:

derive type: single value:AVERAGE(Score) as:'avgScore'

In their unformatted form, the output values are lengthy. You can edit the above transform to nest the value statement with proper formatting using the NUMFORMAT function:

derive type: single value:NUMFORMAT(AVERAGE(Score),'##.00') as:'avgScore'

You might also be interested to know how individual students fared and to identify which tests caused the greatest challenges for the students:

derive type: single value: NUMFORMAT(AVERAGE(Score),'##.00') group: StudentId as: 'avgScorebyStudentId'

derive type: single value:NUMFORMAT(AVERAGE(Score),'##.00') group:TestNumber as:'avgScoreByTest'

To calculate total scores for each student, add the following. Since each individual test score is a whole number, no rounding formatting is required.

derive type: single value:SUM(Score) group:StudentId as:'totalScoreByStudentId'

Results:

Example - Rounding Functions

Example - Other Examples

In the language documentation, there are many other examples where the derive transform is used.