Skip to main content

Table.merge

mergelookup_tablekey_columnsadd_new_columnsallow_unmatched_rowson_problems

Group: Calculations
Aliases: hlookup, join, lookup, vlookup, xlookup

Documentation​

Merges this table with a lookup table New values are looked up in the lookup table based on the key_columns. Columns that exist in the lookup table where a match was found are replaced by values from the lookup table. Columns not found are left unchanged. This operation is similar to Table.update_rows, but just returns a new Table instance, instead of updating the table in-place (which is only possible for Database tables).

Arguments​

  • lookup_table: The table to use for looking up values.
  • key_columns: Specifies the columns to use for correlating rows between the two tables. Must identify values uniquely within lookup_table.
  • add_new_columns: Specifies if new columns from the lookup table should be added to the result. If False, an Unexpected_Extra_Columns problem is reported.
  • allow_unmatched_rows: Specifies how to handle missing rows in the lookup. If False (the default), an Unmatched_Rows_In_Lookup error is raised. If True, the unmatched rows are left unchanged. Any new columns will be filled with Nothing.
  • on_problems: Specifies how to handle problems if they occur, reporting them as warnings by default.

Errors​

  • If this table or the lookup table is lacking any of the columns specified in key_columns, a Missing_Input_Columns error is raised.
  • If an empty vector is provided for key_columns, a No_Input_Columns_Selected error is raised.
  • If a single row is matched by multiple entries in the lookup table, a Non_Unique_Key error is raised.
  • If a column that is being updated from the lookup table has a type that is not compatible with the type of the corresponding column in this table, a No_Common_Type error is raised.
  • If a key column contains Nothing values in the lookup table, a Null_Values_In_Key_Columns error is raised.
  • If allow_unmatched_rows is False and there are rows in this table that do not have a matching row in the lookup table, an Unmatched_Rows_In_Lookup error is raised.
  • The following problems may be reported according to the on_problems setting:
  • If any of the key_columns is a floating-point type, a Floating_Point_Equality.
  • If add_new_columns is False and the lookup table has columns that are not present in this table, an Unexpected_Extra_Columns.

Remarks​

Result Ordering​

When operating in-memory, this operation preserves the order of rows from this table (unlike join). In the Database backend, there are no guarantees related to ordering of results.