Transform Node

About: This article introduces the Transform node, a Compile node within Construct.
Location: Node panel

Table of Contents

• Feature Overview
  • Binning
  • Multi-Variable Formula
    • IF Operation
    • Function Operations
    • Matches REGEX Function
    • Boolean Operations
  • Text Functions
  • Date Time
  • Transform Toolbar
• Function Descriptions
• Video Tutorials
  • Multi-variable formula
  • Binning
  • Text & Date Functions
• Related Articles

Feature Overview

The Transform node allows users to create new variables from existing variables. The original variables used in the calculations will remain unchanged, however new column(s) will be added to the dataset. 

Within the Transform node, there are several options for different types of variables that can be created. Along the top of the Transform window, there are four tabs:

1. Binning: Creating groups of specific values from existing values
2. Multi-Variable Formula: Creating a new variable from existing variables using basic formulas
3. Text Functions: Accessing the values of variables and applying conditions or formulas
4. Date Time: Addressing Date and Time specific variables and applying functions and formulas

Binning

The binning option allows users to create buckets that every record distinctly fits into. To do this, first select the field that the bins will be based off from the list of variables on the left. Next name the first bin/category by entering a name where it says "First Category =". This is the value the new column will contain for each record that fits into this bin. Next, define the definition for the first bin by using the operators from the dropdown list and typing the criteria into the following space.

For example, perhaps users have a field containing SAT Math scores, and want to define low, medium, and high bins. In that case, name the first category "Low SAT." Then define that category as (SAT Math <= 500).

Once defined, click the validate button. Then name and define the next category.

Perhaps a user wants to name the next category as "Medium SAT," and define it as SAT Math > 500 and SAT Math <= 600. Click validate again, and define the next category. Once all categories are defined, type the name of the new column containing the bin values under New Variable Name.

Finally, click Create. This new column now appears at the bottom of the window in the Transform Operations section. Transforms that have already been created can be edited at any time by clicking the name of the transform operation, making any changes, and then clicking the Update.

Tip: If the variable being used to create the binned variable is a categorical variable like state, users can retrieve all of the unique values by clicking the get sample button in the upper right corner. Users can then choose the IN operator, hold down the shift or control keys and choose multiple values to be placed in each bin.

 

Multi-Variable Formula

Multi-Variable Formulas offer the most flexibility in creating new columns as the formulas can make use of variables of varying data types. First, select any variables to be used in the formula by clicking them from the variables list on the left. When a variable is selected from that list, Construct assigns a variable name to each of the selected variables starting with [A] and moving down the alphabet depending on the order of selection. These variables are listed in the Assign Variables section.

Once variables are assigned, any formula can be entered into the formula box such as [A]+[B]. This example would add the two fields.

Tip: If you are unsure how to start writing formulas, check out the Function Definition drop down for a list of all built in functions along with syntax and examples.

Next choose a Result Type from the drop-down box. The default result type is always Real, however if the output contains text and the user leave the result type as Real, the output will contain entirely null values.

Finally, name the new variable in the New Variable Name section and click Create.

IF Operation

An IF statement evaluates a statement and returns a result based on whether the statement evaluates as True or False. The syntax for and IF statement is: IF(P1,P2,P3), where:

P1 is the statement to be evaluated

P2 is the value to return if the expression is True

P3 is the value to return if the expression is False

As an example IF( [A] > 50 , 'High' , 'Low' ) would return the value of High if the variable [A] was greater than 50, and Low if it was not greater than 50.

Note: As seen in this example, anytime a text value is included in a formula it should be surrounded by single quotes and the Result Type should be set to Text. 

Function Operations

Clicking the f(?) button on the right side panel displays a list of functions. Clicking a function shows the definition and parameter requirements of the function. The function can either be typed directly into the formula box, double clicked, or the caret to the right of the function can be used to populate the function into the formula box.

Tip: Notes can be included within the formula window which will not be considered part of the formula. Notes are denoted by /* to open the note and */ to close the note.

Matches REGEX Function

MatchesRegEx is a function that takes a text column and returns a "1" when the text string matches the desired Regular Expression (RegEx). In effect, MatchesRegEx acts as a universal IS function, although users must develop the testing expression. Fortunately, regex syntax is well documented online. 

Examples

• This RegEx will only match a valid social security number (i.e. it won't match 000-00-0000): ^(?!000)([0-6]\d{2}|7([0-6]\d|7[012]))([ -]?)(?!00)\d\d\3(?!0000)\d{4}$
• This RegEx will match currency (with or without cents, and optional $ sign): ^\$?\d+(\.(\d{2}))?$
• This RegEx will match positive integer: ^\d+$

Boolean Operations

Users can create Boolean (True/False or 0/1) variables in the Multi-Variable tab of the Transform node.

To do this, type an expression including a variable that will either evaluate to True or False. Then select a Result Type of either Boolean (which results in True/False) or Integer (which results in 0/1).

Examples

A>0

This formula results in a True if A is greater than 0, and results in a False otherwise.

A>0 AND B>0

This formula results in a True if A and B are both greater than 0, and results in a False otherwise.

A>0 OR B>0

This formula results in a True if A or B is greater than 0, and results in a False otherwise.

 

Text Functions

To use a Text Function, first choose a variable from the left side that contains text values. If there are no variables that are type text, this list will be blank. Next, fill in any parameters that are requested. Examples of the function definition as well as the parameter definitions can be found in the Function Definition box. Once the required parameters are filled in, name the new transform and click the Create button.

 

Date/Time

Date/Time functions will create a new column that contains additional information relating to a date variable that already exists in your dataset. For example, if a date exists in your dataset, you can create a new variable that will calculate the day of the week that date is within the year. Other date/time functions are listed under the Transforms function list on the right side of the window. If no date/time fields are found, the column list is blank.

 

Transform Toolbar

Editing a Transform

Transforms can be edited at any time by clicking the specific transform formula within the Transform Operations section at the bottom of the window. Once the edits have been made, click the Update button to update and save the changes. A transform formula can also be removed by right-clicking the formula and clicking Delete. Several formulas can be selected at once by holding the Ctrl key and clicking more than one transform formula.

 

Formula Parameter

Parameters are user-defined named variables that are specific to each job. Parameters can be configured under the Edit Parameters button of the Job Toolbar. They can they be used throughout several nodes including within Transform operations by clicking the green "P" parameter icon. To learn more about job parameters, click here.

 

Test Formula

The Test Formula feature allows users to preview the result of the transform formula depending on different values of the variables that are being used in their formula. Clicking the Get Values button will populate the unique values of the variable within the Test Value drop-down menu. After specifying Test Values, click the Test button to view the output of the formula using the specific Test Values in the Result window. This is a useful feature to test your formula as you craft it, rather than running your job and observing the result to check your work.

Validate Formula

This button appears above the formula box. Clicking this button will check the contents of the formula box for syntax errors. If a problem is detected, an Invalid Formula error is displayed. If no message appears, then the syntax is valid.

 

Exporting and Importing a Transform Operation

Transform formulas can be exported by selecting the Transform Operation and the clicking the Export icon shown in the top left of the window or by right clicking on the Transform Operation name and selecting Export transform. Transform formulas are saved as a *.ritd (Rapid Insight Transform Definition) file.

Formulas can also be imported using the Import icon shown in the top left of the Transform node window, or by right-clicking in the Transform Operation list at the bottom of the window and selecting Import transform. You can then browse to and select a saved *.ritd file and its operation will be added to the existing Transform Operation list.

Function Descriptions

Video Tutorials

Binning

Text & Date Functions