KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the CrossJoin Operator to Cross Products.

The Cross Join node combines two input flows and produces the cartesian product of the sets. The output is typed using the merge of the two input types. If the right type contains a field already named in the left type, it will be renamed to avoid collision.

Use this node with caution. It produces a product of its two input data sets. Even with fairly small data sets, the amount of output data can be large.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the Group Operator to Compute Aggregations.

The Group node aggregates data within key groups. A set of aggregation functions is available, including average, count, minimum value, maximum value, standard deviation, and others. If no key fields are provided, the aggregation functions are applied to all of the data as one large group.

The node uses groups of consecutive equal keys ("key groups") to determine which data values to aggregate. The input data need not be sorted; if it is already sorted, performance will be optimal.

The output of the Group node will include the key fields (if specified) and the outcome of each aggregation function. A row of output is generated for each distinct set of key values. If no key fields are specified, a single row will be output with the aggregation results for all input rows.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the Join Operator to Do Standard Relational Joins.

The Join node joins two inputs by the specified key fields. Inner joins and the various types of outer joins are supported by specifying the join type.

In all join modes, repeated key values on the left and right sides are supported. If a key group contains multiple rows on both sides, then all combinations of left and right rows in the key group appear in the output for that key group. Note that in this case, memory consumption is lower if the side with more key repetition is linked to the left input.

When using the Hash Join option, link the smaller of the two data sources to the right input to reduce memory consumption. When using Hash Join, there is no requirement to sort the input data.

The Join Condition can optionally be set using the Simple Predicate view or Predicate Expression view.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the UnionAll Operator to Create Union of Data Sets.

The Union All node combines two input flows into a single flow. Usually no guarantee is provided as to the ordering of the rows in the resulting flow. The operator attempts to provide data with the minimum of delay, following a first-come, first-served policy as closely as possible. However, if the data order specified in the metadata of both input flows is the same, then the node will attempt to preserve the sorted data order. The metadata will have its data order specified if the flow has gone through a Sort or Assert Sorted node.

The output type can be provided by using the Schema Editor. If a schema is not provided, the output type, including the field names, can be automatically derived based on the settings provided.

If a schema is provided, the inputs will be manipulated to match the output schema. The fields are mapped from the inputs to the output by field name. Fields contained in an input but not in the output will be dropped. Fields contained in the output but not in an input will contain null values. The input fields will be converted to their matching output field type as needed. If a conversion is not supported, the configuration will fail.

For example, if an input field has the type string and the output type is a date, the input values will be converted according to the schema specification for the output field. In the case of a date, a pattern to use for the conversion can be provided. If one is not provided, a default pattern will be applied.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the FilterExistingRows Operator to Filter by Data Set Membership.

Filters the left input based on the presence of key values in the right input. Performs both a semi-join and anti-join of the data.

In the semi-join, only rows from the left that match one or more rows on the right using the keys are output, whereas in the antijoin, only rows from the left that match no rows on the right are output.

The Join Condition can optionally be set using the Simple Predicate view or Predicate Expression view.

In the Simple Predicate view:

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the FilterRows Operator to Filter by Predicate.

Filters rows from the source input based on the predicate defined in the configuration. Records passing the predicate—those for which the predicate evaluates to true—are emitted on the standard output.

Records that fail the predicate are emitted on the rejected output.

The filter predicate can be defined in two different ways. In the Simple Predicate view, the filter is defined as a compound statement of simple predefined field predicates. In the Predicate Expression view, the predicate is defined using a built-in expression language, allowing for arbitrarily complex predicates to be defined. The Expression Language is defined at Expression Language.

To use the Simple Predicate view, select the appropriately named tab. The field predicates are displayed as a table. The first column chooses the field to which the predicate applies. The second chooses the predicate. The third provides an additional argument to the predicate; some predicates only require a single input, in which case the third column is not editable for the row. When editing the third column:

The individual field predicates are combined to produce the filter predicate according to the setting of the radio buttons below the table. If the All Conditions True option is selected, all the predicates must be true; the field predicates are combined using a logical and. If the Any Conditions True option is selected, at least one predicate must be true; the field predicates are combined using a logical OR.

To use the Predicate Expression view, select the appropriately named tab. A text area will be displayed in which you can enter an expression. If you are moving to this view from the Simple Predicate view, the expression is initially equivalent to the one defined in the simple predicate table. Because the simple view cannot represent all predicates, when you are editing the expression in the Predicate Expression view, the simple predicate tab is disabled. It can be reenabled only if:

In the Simple Predicate view:

In the Predicate Expression view:

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the LimitRows Operator to Limit Output Rows.

Select a subset of the rows from the input based on an initial offset and maximum number. Only rows that are positioned within the specified range are output.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the SampleRandomRows Operator to Sample Data.

This node randomly samples rows from the input data set. Selected rows are pushed to the output of the node. Two modes for sampling are supported: Relative and Absolute.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the RemoveDuplicates Operator to Remove Duplicates.

The Remove Duplicates node removes duplicate rows based on a specified set of group keys. The “first” record of a key value group is pushed to the output. Other records with the same key values are ignored. The “first” record of a key group is determined by sorting all rows of each key group by the specified sort keys. If the sort keys are unspecified, then an arbitrary row is output.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the SelectFields Operator to Select Fields.

Constructs a new record flow with a subset of fields from the source record. Use the Selection check boxes to include or exclude fields, and use the Fields (renamed) text boxes to rename them. The resulting record flow has exactly those fields selected and is in the same order as specified in the selection table, with the new names where applicable.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the AssertSorted Operator to Assert Data Ordering.

This node asserts that input data is already sorted based on configured source fields and their sort order. Null values sort higher than non-null values under ascending order, lower under descending order. If the data is not sorted, the node will fail during execution.

Using this operator can help avoid unnecessary sorts in nodes connected to the output, as consumers are guaranteed that the data they receive is sorted as specified.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the ColumnsToRows Operator to Convert Columns to Rows (Unpivot).

The Columns To Rows node normalizes records by transposing values from columns into multiple rows. You define one or more pivot value families to describe the output fields and the source fields that will be mapped to these fields in the output. Target field datatypes are determined by finding the widest common type of the pivot elements. An exception can occur if no common type is possible. The number of elements defined in a Key Family or Value Family must be equal.

You may optionally define a single pivot key family. This lets you provide a context label when transposing rows defined in value families. You set a list of Strings that correspond positionally to the fields defined in the list portion of the defined value families. The size of all lists across both value families and the key family must be the same.

You may optionally define Group Key Fields that will be the fixed or repeating value portion of multiple rows. If this property is unset, group key fields will be determined by the remainder of the source fields not specified as pivot elements.

The following table provides an example set of data that we want to unpivot to a more normalized contact, type, address, and number format.

To accomplish this unpivot, a pivot key family will be created with the field name of "type" and the labels "home" and "business". We will define two pivot value families with the names "address" and "number". The address family will contain the fields "HomeAddress" and "BusinessAddress", which will map to the home and business types we defined as the pivot key family. The number family will contain the fields "HomePhone" and "BusinessPhone", which will similarly map to the family types. Finally, we will set the "Contact" field as a group key so it will be included in each row. This produces the following unpivoted table.

If, during the columns-to-rows process, all mapped values to the pivot columns are found to be null, that row will not be produced in the output. In the example above, if Pivot Key Family was not defined, the Sally White "home" record would not have appeared in the output since both elements are null. Only because a Pivot Key Family was defined does this record appear in the output.

 KNIME  This topic describes a KNIME node. It is based on a variation of the DataFlow operator DeriveFields, described in Using the DeriveFields Operator to Compute New Fields.

The date value extraction node extracts individual values from either a date or a timestamp data type. Multiple values may be extracted from each valid input field. Select the option on the configuration dialog to add a new extraction specification. After a new extraction is added, you can select the input field and the type of value to extract and set the output field name.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the DeriveFields Operator to Compute New Fields.

Derives new fields from existing fields, using specified expressions.

Each output field is given an expression to be evaluated, and the result of that evaluation is placed in the output field. Input fields are passed to the output unless they are overridden or Drop Underived Fields is selected. Input fields can be overridden by creating a field derivation with the same output field name.

The expressions must follow the DataFlow expression language. This language supports standard equivalence operators (=, >, <, >=, <=), Boolean operators (and, or, not), arithmetic operators (+, -, *, /), and many more functions. For information about expression syntax and functions, see Expression Language.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the ReplaceMissingValues Operator to Replace Missing Values.

The missing values node replaces missing values in the input data using the configured set of actions. Actions may be based on supported data types or by field name. If specified by type, the action is applied to all fields of the given type within the input data.

The following actions are supported:

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the NormalizeValues Operator to Normalize Values.

Normalizes values for each of the selected input fields on each input row. A new field is added to the output for each input field selected.

Statistics such as the mean and the standard deviation are calculated for each of the selected fields and used during the normalization calculation.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the PartitionHint Operator to Explicitly Partition Data.

Repartitions data across execution nodes. It can be used with the DataFlow Executor to control how DataFlow graphs are broken into sequentially executed segments. It is used for advanced performance-tuning techniques. Typically, using this node is not required.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the Randomize Operator to Randomize Partitioning.

The Randomize Order node reorders the input in a random fashion. The output is identical to the input, but ordered randomly and with each record on a random partition.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the Rank Operator to Rank Data.

Rank Fields is a node that ranks data using the given rank mode. The data is grouped by the given partition fields and is sorted within the grouping by the ranking fields.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the DeriveFields Operator to Compute New Fields.

Performs regular expression operations on text input. The output from the regular expression is placed in the column specified by the Replacement Column Name field. If no field is specified, then a new column is created.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the RowsToColumns Operator to Convert Rows to Columns (Pivot).

The Rows To Columns node is used to pivot data from a narrow representation (rows) into a wider representation (columns).

The data is first segmented into groups using a defined set of group keys. The ordering of the group keys is important, as it defines how the data is partitioned and ordered for the pivot operation. A pivot key field provides the distinct values that will be used as the pivot point. This field must be a string or enumerated type. A column is created in the output for each distinct value of the pivot key.

An aggregation is defined, which is performed on each data grouping defined by the group keys and the pivot key. The result of the aggregation for each unique value of the pivot key appears in the appropriate output column.

The following table provides an example set of data that we want to pivot by Region. There are only four regions: North, South, East, and West. For each item, we want to compute the total sales per region. Items can show up multiple times in a region because the data is also organized by store.

To accomplish this pivot, the ItemID will be used as the group key. The Region will be used as the pivot key. And the Sales column will be the pivot value, aggregating by summing the values. The pivot key values are "North", "South", "East" and "West". The result of the pivot is shown in the following table.

Note that the sales total for the West region for item 1 is empty. Scanning the input data shows that no sales were present in the West region for item 1. Item 1 did have two sales values for the North region. Those values (1000 and 800) are summed and the total (1800) appears in the North region column for item 1. Values with a ? indicate a null or nonexistent value.

The key concepts to understand in using the Rows To Columns node are:

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the RunJavaScript Operator.

The Run JavaScript node lets you provide JavaScript logic to be executed for every input record. This node is useful when you want to transform the input data in some way and a node does not exist that provides that transformation.

You can also supply a script to run before the first record is handled and after the last record has been processed. The first can be used to initialize variables, connect to a database, define a function or other initialization steps. The script run after the last record can be used to clean up as needed. Neither of these two scripts can access the record data.

You define output fields through the Schema Editor, setting field name and data type. You can optionally import existing schemas or export schema information you have created. You can then add assignment statements in the On Every Record script. The last value set on an output field is pushed to the output. You can reference input fields by name within the script.

For example, we want to calculate the difference between two input fields that are doubles and have the result pushed to an output field using JavaScript. The input fields are named field1 and field2. First, define an output field in the Schema Editor dialog. Ensure the type of the output field is set to double. For this example, the output field will be called diff. The JavaScript code for creating difference follows:

This is a simple example but demonstrates how to set an output field so that its values appear in the output data of the node. If you set an output variable multiple times, the last value set will be output. If an output variable is not set, it will contain the NULL value.

The field names defined in your source and target schemas are mapped as JavaScript variables during execution and as such are required to comply with JavaScript naming conventions. Variables must start with a letter or '$' character and be composed solely of word characters [a-zA-Z_0-9]. Field names that do not meet this format will be highlighted in red in the source and target schema field panes. The RunJavaScript node will attempt to rectify this at runtime by substituting a compliant alternative name that the engine can correctly resolve.

When composing your script in the editor pane, you can insert a field name by double-clicking on it from the source or target field panes.

In cases where the field name is non-compliant, the name substitiution function is automatically applied to the field name when it is inserted in the script. The substitution convention is as follows:

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the RunRScript Operator to Invoke R Scripts.

The Run R Snippet node allows writing R code to process the input data, producing a result that will be pushed to the output. The R script will be passed a data frame in the variable named R. Each field of the input data set will be represented as a column in the input data frame. The R code can use the data frame containing the input data as needed. The results should be placed back into a data frame using the R variable name again.

Note that all of the input data will be gathered and loaded into the R environment at run time. This implies the data must fit into memory within the R memory space.

The R installation must be installed and configured on every machine that will execute this node. When run in distributed mode, the R installation must be installed on each worker node using a consistent installation path. The node requires that the path to the Rscript executable within the R installation be set. The Rscript executable is invoked to run the R code.

This node is parallelized by default. This implies that the R code contains no data dependencies as multiple instances of the node will be executed at run time. Each instance will handle a sub-set of the input data depending on the current distribution and ordering of the data. If your R code has data dependencies and cannot be run in parallel (or distributed) then check the Disable parallelism box. Disabling parallelism will likely have a negative affect on performance.

The output schema (type) of the node must be set if the R code outputs a data frame with a different schema than the input data frame. Create new output fields and specify their types to set the output schema. Your R code must output a data frame that matches the defined output schema. If a mismatch is found, a run time error will occur.

Two variables are preset into the R environment. The variable partitionID is a zero-based identifier of the partition containing the current instance of the R snippet operator. The variable partitionCount specifies the total number of data partitions in the current execution environment. These variables are both numeric and can be used when partition information is needed within the user provided R script.

The field names defined in your source and target schemas are mapped as R variables during execution and as such are required to comply with R naming conventions. Variables must comply with the following regex or undergo automatic variable name remapping: ^[\\.]?[a-zA-Z_]+[\\.0-9a-zA-Z_]*$. Field names that do not meet this format will be highlighted in red in the source and target schema field panes. The Run R Snippet node will attempt to rectify this at run time by substituting a compliant alternative name that the engine can correctly resolve.

When composing your script in the editor pane, you can insert a field name by double-clicking on it from the source or target field panes. In cases where the field name is non-compliant, the name substitiution function is automatically applied to the field name when it is inserted in the script. The substitution convention is as follows:

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the RunScript Operator.

The Run Script node allows you to provide a script that will be executed for every input record. The language of the script can be selected. Several of the primary scripting languages available on the JVM are supported. This node is useful when you want to transform the input data in some way and a node does not exist that provides that transformation.

You can also supply a script to run before the first record is handled and after the last record has been processed. The first can be used to initialize variables, connect to a database or other initialization steps. The script run after the last record can be used to clean up as needed. Neither of these two scripts can access the record data.

Define output fields by adding a new output field, setting its name and type. You can then add assignment statements in the “On Every Record” script. The last value set on an output field is pushed to the output. You can reference input fields by name within the script.

For example, we want to calculate the difference between two input fields that are doubles and have the result pushed to an output field using JavaScript. The input fields are named field1 and field2. First, define an output field in the configuration dialog. Ensure the type of the output field is set to double. For this example, the output field will be called diff. The JavaScript code for creating difference follows:

This is a simple example but demonstrates how to set an output field so that its values appear in the output data of the node. If you set an output variable multiple times, the last value set will be output. If an output variable is not set, it will contain the NULL value.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the Sort Operator to Sort Data Sets.

Sorts input data based on configured source fields and their sort order. For ascending order, null values sort higher than non-null. For descending order, null values sort lower than non-null.

The Sort node may not be needed, since other nodes often explicitly specify data distribution and data ordering. For example, the Join node has a Hash Join setting that takes care of sorting input, so you do not need to insert a sort upstream. However, you may need a sort before nodes such as Run JavaScript, where execution of JavaScript code may have a data order dependency that the DataFlow execution environment is not aware of.

The Sort node is commonly used when results are written to a file, by inserting it just before the final writer node to achieve the needed output order.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the SplitField Operator to Split Fields.

Splits a string field into multiple fields using a specified delimiter. The output fields are specified by mapping split indices to field names.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the DeriveFields Operator to Compute New Fields and Available Functions.

Creates length-based substrings from an existing string fields and overlays them on the output. Setting the Target field name value to an existing Source field will effectively overwrite that Source field with the resultant substring. A null input value results in a null output value.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the DeriveFields Operator to Compute New Fields and Available Functions.

The Time Difference node provides time difference calculations that you configure. Several calculations can be created to be executed by the node. The calculations may be configured to compare two input fields or an input field and a constant value or the current time at execution. Two time values must be provided, a start time and an end time. The calculation will compute the difference between the end time and the start time. You must specify the value type for the start and the end times. Valid values are:

The start and end values must be of compatible types. A difference cannot be taken between a date and a time of day. If one value represents a date or time of day, the other may be a timestamp, in which case the timestamp is truncated to the matching type based on the default time zone.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the DeriveFields Operator to Compute New Fields and Available Functions.

Trims leading and trailing whitespace from the specified String fields in the Input record flow.

 KNIME  This topic describes a KNIME node. For the DataFlow operator it is based on, see Using the DeriveFields Operator to Compute New Fields and Available Functions.

The Type Conversion node constructs a new record flow with the original input fields converted into the specified types. Only supported type conversions are available for use.

The formatting pattern that should be used by the conversion function can also be specified. If the formatting pattern is not specified, the default conversion and formatting behavior will be used. The converted fields will have the same names as the original fields.