Merge/Join

The Merge/Join operation allows you to merge the data from two data-sets together to create a single data-set.

In most scenarios merging two data-sets requires that the two tables have at least one common field, and that at least one of these fields contains no duplicate records. If you intend to merge on a Date/Time field the storage/display formatting of both merge fields must be identical. 

Join by matching records where...

This allows you to specify the join criteria by selecting the matching fields from both datasets. You can define multiple join criteria; each criteria specifies a single match. To add a new join criteria click on the Add join criteria button. When you select a field to match on the number of unique records in that data-set are shown. In most cases one or both of the match fields should contain no duplicate values. If both fields contain duplicate values the merge may result in a huge number of records. Use the Accent sensitive and Case sensitive options to determine whether accent characters or case have a bearing on whether a value from the first data-set matches a value from the second. 

Which record sets do you want to retain?

This determines which records are included in the merged data based on the join criteria you specified. You can select any combination of these options. 

• Non-matching records from data-set 1. Select this option to include all records from the first data-set that did not match any records from the second data-set.
• Merged-records. Select this option to include all records that matched in both data-sets.
• Non-matching records from data-set 2. Select this option to include all records from the second data-set that did not match any records from the first data-set. 

If there are any conflicting field names...

This alllows you to determine what action Omniscope should take if there are any fields outside of the join criteria with matching names. 

• Add new field names. If a duplicate field name is found then a new field will be created with a new unique name.
• Keep values from data-set 1. If a duplicate field name is found only the field from the first data-set will be retained.

Source field

Select this option to create an additional field that lists the name of the input data-set each row in the data originated from. For merged records both data-sets will be listed.

Example

In this example we will be merging the data of two tables. The first table contains a list of customers. The second table contains a list of transactions made by the customers during January. The input data for the Merge operation is shown below.

In order to merge these two data-sets we need to identify the join criteria. In this case both tables have a common field: Customer ID. We therefore need to create a merge operation with a single Join criteria, matching on records where Customer ID from the customers table matches Customer ID from the transactions table. 

The DataManager configuration for this operation is shown below.