Value Lookup

The node has two inputs: a data table and a dictionary table.
From the data table select the column that is used to look up values in the dictionary table.
From the dictionary table select a column that contains the search keys or criteria.
When a lookup value matches an entry in the dictionary, cells from that row are appended to the data table. In case multiple rows match you can choose if you want to use the first match or the last match. If no rows match you can choose to insert missing values, match the next smaller value or match the next larger value.
Missing values are treated as ordinary values, i.e. they are valid as lookup and replacement value. The key column of the dictionary can also be a collection type. Then, the values in the collection act as alternative lookup values for the associated row.

In the output section you can select the columns in the dictionary table that shall be inserted in the output data table.

Options

Lookup column (data table)

The column in the data table that will be used to look up cells in the dictionary.

Key column (dictionary table)

The column in the dictionary table that contains the search key / criterion.

If multiple rows match

Defines the behavior in case there are multiple matching keys in the dictionary table.

If no row matches

Defines what happens when a lookup key is not present in the dictionary: If "Insert missing values" is selected, missing values are inserted. If "Match next smaller" ("- larger") is selected, the next smaller (larger) value from the dictionary is matched, based on the value of the lookup key. If no such element can be found, a missing value is inserted.

String matching

The matching behavior when matching strings: Full string matching matches a lookup string only if it exactly matches a search string. Substring matching matches a lookup string if the key in the dictionary is a substring of it. Wildcard and Regex matching match a lookup string if a pattern in the dictionary matches it.

Match strings case-sensitive

When enabled, the string matching will be case-sensitive, otherwise case-insensitive.

Lookup column output

Defines the content of the column that is selected as lookup column (data table). If "Retain" the content of the lookup column is left unchanged. If "Replace" is selected, the cell contents are replaced with values from the dictionary table. If a match is found, the selected column's value is inserted, otherwise the original value can be kept or a missing value can be inserted. The name of the column does not change. If "Delete" is selected, the lookup column is removed entirely from the output table.

Replacement column

The column from the dictionary table that provides the new values for the lookup column in the data table.

If no match found

Defines the content of the lookup column if no match is found in the dictionary table. If "Retain" is selected, the cell is left as is. If "Insert missing" is selected, a missing value is used as content for the cell in the lookup column.

Append columns (from dictionary table)

The columns in the dictionary table that contain the values added to the data table.

Append a column indicating whether a match was found

When checked, a new column "Match Found" is appended to the output that contains a boolean indicating whether a match was found.

Views

This node has no views

Workflows

• 00_Combine_Clean_and_Summarize_Spreadsheet_DataKNIME Hub
• 01 Development WorkflowKNIME Hub
• 01_Flow Variable and Component - ExerciseKNIME Hub
• 01_Flow Variable and Component - ExerciseKNIME Hub
• 01_Flow Variable and Component - ExerciseKNIME Hub
• Show all 231 workflows

Links

• No links available

Developers