Column Expressions

This node provides the possibility to append an arbitrary number of columns or modify existing columns using expressions. For each column that shall be appended or modified a separate expression is defined. These expressions can be simply created using predefined functions similar to the Math Formula and String Manipulation Nodes. Nevertheless there is no restriction on the number of lines an expression has and the number of functions it uses. This allows the user to create their own complex expressions.
Additionally, intermediate results of functions or calculations can be stored within an expression by assigning them to variables (using '='). This allows results to be reused in different parts of the expression after they have been assigned (see Examples). Note: the names of these variables must be different from all of the names of the predefined functions. Otherwise an error will occur.
To add/remove an expression, the "+"/ "-" buttons respectively have to be used. To edit an expression for an output column, the expression has to be selected in the list of expressions and can be modified in the Expression Editor.
The created expressions are executed row-wise on the input data. Available flow variables and columns of the input table can be accessed via the provided access functions variable("variableName") and column("columnName"). Newly created columns are appended in the order they were defined (top to bottom) whereas replaced columns will remain at the original position of the input table. For each expression the last computed instruction will be returned.

The syntax and grammar of the expressions are based on the JavaScript Languange .

Examples:

  • 5*7+3

    will append a column with the value 38 for each row.

  • salary = column("salary")
    salary + salary*0.1

    will append a column with the values of the salary column of the input table increased by 10%.

  • "a1"
    "a2"

    will append a column with the value "a2" for each row, whereas

    a1 = "a1"
    a2 = "a2"
    a1

    will append a column with the value "a1" for each row.

  • and(column("age") < 26, not(column("student"))

    will append a column with true values for everyone under the age of 26 who is not a student, and false for every other person. In this case and(...) and not(...) are predefined functions provided by the Expression Editor.

Options

General

+
Adds an expression that can be defined.
-
Removes the selected expression.
Moves the selected expression up. This will influence the order of the output columns.
Moves the selected expression down in the order. This will influence the order of the output columns.

Expression Table

Expression
Shows the currently defined expression for the specified output column.
Type
Shows the defined type that shall be returned by the script. The resulting column will have this type in the output table.
Collection
If checked the defined output column will be a collection of the selected type.
Replace Column
If checked the defined output column will be replaced. If unchecked the output column will be appended.
Output Column
The name of the output column in the resulting table.

Expression Editor

column
This opens a list containing all available columns of the input table. The selected column can be inserted by hitting ENTER or by double clicking the column. This menu can be also accessed via the shortcut 'ALT + c' (Windows & Linux) or via 'CTRL + OPTION + c' (Mac) and closed with 'ESC'.
variable
This opens a list containing all available flow variables. The selected flow variable can be inserted by hitting ENTER or by double clicking the variable. This menu can be also accessed via the shortcut 'ALT + v' (Windows & Linux) or via 'CTRL + OPTION + v' (Mac) and closed with 'ESC'.
function
This opens a list containing all available predefined functions together with their description. The selected function can be inserted by hitting ENTER or by double clicking the function. This menu can be also accessed via the shortcut 'ALT + f' (Windows & Linux) or via 'CTRL + OPTION + f' (Mac) and closed with 'ESC'.
Evaluate
Clicking on this button triggers evaluation of the current expression on the first row of the input table (if available) and displays the result.

Advanced

Enable multi-row access, window size
If selected the script's column(Object, int) method enables access to column values in preceding or following rows of the current row. The second argument (int offset) thereby defines the number of rows to look "behind" or "ahead". Since this can be a costly operation (data needs to be cached) a window size needs to be specified. For instance, a value of 5 will cause the node to cache 5 rows prior the current row and 5 rows following the current row. Attempting to access a value outside this window will cause the node to fail.
For rows prior to the first row (or after the last row), use...
For the first and last rows in the table, the multi-row access may address rows outside the value range (e.g. column(0, -3) on the first row). The option Value in the first (last) row will assign the values from the first row when accessing rows with negative row index, and values from the last row when accessing values from rows outside the table dimension. The option Use 'null' will provide their values assigned to null in the script.
Fail on script error
If checked the node will fail if an error occurs during execution.
Fail on invalid column/variable access
If checked the node will fail if an invalid column/variable access occurs during execution (this is the default). An invalid access can occur by providing either an unknown name (column/variable) or an index that is too high/low (column).

Input Ports

Icon
Any input table. For each row the defined expressions will be computed and their results will be inserted to the specified column.

Output Ports

Icon
Output table.

Popular Predecessors

Views

This node has no views

Workflows

Links

Developers

You want to see the source code for this node? Click the following button and we’ll use our super-powers to find it for you.