Combining Multiple Streams of Data (Append vs. Merge or Join)

Introduction

To combine multiple streams of data, FME provides users the capability to append or merge (or join) their data. The two techniques, although seemingly similar yield different results. When datasets are appended, additional rows are added to the attribute table. However, when datasets are merged or joined, additional columns are added.

Append

Datasets are typically appended when there is no change to the table schema or data model. Appending can use the same schema since the values of one dataset are added after the existing values of another.

If the table schemas are different, the output will contain missing values for the columns not contained in both datasets.

Appends should be used in instances where additional rows or records are added to the dataset. Appends are performed by the Writer.

The example workspace appends records from both tables into a single table.

Merge or Join

Datasets should be combined by merging when additional attributes need to be added to the table schema or data model.

Merging requires a common attribute to join on, this ensures that the new attributes are correctly matched within the output. There can be missing values within either dataset provided the attribute being merged upon does not contain missing values.

Datasets can be merged using a variety of transformers, including the FeatureMerger (depicted below) and the FeatureJoiner. To join datasets using their spatial location, use the SpatialFilter or SpatialRelator.

The example workspace merges the attributes from both tables into one table.

Example Workspace

The example workspace both appends and merges Excel spreadsheets containing Vancouver tree data.

Tables with different Vancouver neighborhoods are appended to produce a table containing both neighborhoods with their tree data.

The Downtown Trees table is merged with the Genus Species table by CommonName to produce a table with the fully named trees. The join between the two tables uses the FeatureMerger to add additional columns to the Downtown Trees table.

Additional Merge Articles

QA of Spatial Concepts with FME

Merging Polygons with the Dissolver Transformer

The DatabaseJoiner Transformer