Using the Premium Derived Column Component

The Premium Derived Column Component offers a familiar UI to that of the built-in Derived Column Component. Like the built-in component, the user will add derived columns and utilize expressions to transform data. The following are some of the additional features that the Premium Derived Column has:

  • Nearly 200 functionalities (with more to come)
  • Advanced Expression Editor
  • Expression validation and testing with test values
  • Advanced data type detection

Main Editor

The Main Editor is where the user can add and removed derived columns. To add a derived column, just start editing the last blank row. To remove either press 'Delete' or right click and select 'Delete'.

Premium Derived Column Editor

List of Columns & Variables

Drag and Drop a column or variable from the tree view to the Expression column to add a column or variable to your expression.

List of Functions

Drag and Drop a function, cast, or operator from the tree view to the Expression column to add a function to your expression. Information about the selected function will appear in the panel below.

Search For Function

Filter the list of functions.

Reevaluate Expressions

This will evaluate and validate every expression in the data grid view.

Columns Page

The Columns Page is where derived columns are added and given expressions. Add a derived column by editing the last row in the data grid view and a new row will automatically be added. Remove a derived column by selecting a row and pressing the 'Delete' key or right click and select 'Delete'. The data grid view contains the following columns:

Derived Column Name

The name of the output column that will contain the result of the specified Expression

Derived Column

The input column to derive from meaning the output value of the selected column will be replaced with the result of the specified Expression. To instead add a new output column select '<add as new column>'.

Expression

The expression that will be evaluated for each row and sent to the output column specified in Derived Column Name. When editing the Expression, click the '...' button to launch the Expression Editor. Construct an expression by using:

  • Columns - Specify the column name in square brackets or the column LineageID prefixed with a '#'. During runtime this is replaced with the value of the specified column of the current row.
  • Variables - Specify the fully qualified name of the variable in square brackets and prefixed with a '@'. During runtime this is replaced with the current value of the specified variable.
  • Functions - Specify the function name followed by parentheses. Inside the parentheses should be the respective functions required parameters. During runtime this is replaced with the result of the function.
  • Casts - Specify the data type in parentheses. The cast may also require parameters, specify those inside the brackets after the data type separated by commas (,). During runtime this will attempt to cast the value to the right to the specified data type.
  • Operators - Specify different unary, binary, or ternary operations to perform during runtime.
  • String Literals - Specify strings in the expression by placing text inside of double quotation marks (").
  • Numbers - Specify numbers (integers and decimal) in the expression as is.
Data Type

The data type of the derived column. This is not editable because we automatically detect the datatype based on the expression. You can change the data type by using a type cast from the list of functions tree view.

Length, Precision, Scale, Code Page

Like the Data Type column, these properties are automatically detected but these columns are editable based on the detected data type.

Error Handling Page

The Error Handling page allows you to specify how errors should be handled when they happen. 

Premium Derived Column Editor - Error Handling

There are three options available. 

  1. Fail on error
  2. Redirect rows to error output
  3. Ignore error

When the Redirect rows to error output option is selected, rows that failed to be sent will be redirected to the 'Error Output' output of the Transformation Component. As indicated in the screenshot below, the blue output connection represent rows that were successfully sent, and the red 'Error Output' connection represents rows that were erroneous. The 'ErrorMessage' output column found in the 'Error Output' may contain the error message that was reported by server or the component itself.

Premium Derived Column Editor - Error Output

Expression Editor

The Expression Editor offers a multi-line colored textbox to construct more complex expressions. Expressions can also be tested using test values for column data.

Premium Derived Column - Expression Editor

List of Columns & Variables

Drag and Drop a column or variable from the tree view to the Expression column to add a column or variable to your expression. Below the list of columns there is a panel with details about the selected variable or column. When a column is selected you can change the value of Test Value. This is the value that will be used when the expression is tested.

List of Functions

Drag and Drop a function, cast, or operator from the tree view to the Expression column to add a function to your expression. Information about the selected function will appear in the panel below.

Search for Function

Filter the list of functions.

Expression Textbox

The expression that will be evaluated for each row.

Validate

Validates the expression using test values. Any errors will appear in the error box below.

Test

Tests the expression using test values and displays the result in a message box.