Merging data from another table

Peek

In EasyMorph it's not possible to reference other tables in expressions, however it is possible to bring a value from another table and append it as a new column with the help of Peek transformation. Further, this new column can be used in expressions. The Peek transformation is the most basic data merging transformation in EasyMorph.

Peek transformation

Lookup

Lookup transformation is a close equivalent of VLOOKUP function in Excel. It allows looking up and returning values from another table. Unlike Excel's VLOOKUP besides adding a new column the transformation can also replace an existing column.

Lookup transformation

Merge

Merge another table transformation can be used in more complex cases. It merges (joins) one or more columns from another table, based on whether one or more key fields in the current table match respective key fields in the other table. The transformation can operate in three modes:

  • Lookup mode — use the first row with matching combination in the secondary table, and ignore others. No new rows are created in the primary table. This mode is essentially a multi-column VLOOKUP (if Excel had one).
  • Left join mode — use all rows with matching combination in the secondary table; it will create new rows in the primary table. This mode is equivalent to the left join in SQL.
  • Full join mode — this mode is equivalent to the full outer join in SQL.

The example below merges full state name from another table by linking the columns with two-letter state codes.

Merge transformations

See our web-help for the full list of transformations.


Read next: Derived tables