Using the Merge Data Wizard (2.5)

Performing merges: Joins and Appends/Concatenations

To merge data from another file into your own Omniscope file, you must have at least one column in your data matching one of the columns in the external merge file(s). For example, to use a data file such as our By Countries merge files, you must have at least one column in your data that has the names of countries, or their ISO codes, or other standard identifiers. 

Note: Sample IOK merge files containing decimal geographic coordinates for countries and major cities, plus examples of other useful reference data available to merge into your data files are available here.

Using the Merge Data Wizard

To open the Merge Data wizard, click Main Toolbar: Data > Merge and the Merge Data wizard will appear:

1. Choose external data file - point to the external merge file containing the fields (columns) you wish to add to your data set. You will need to specify what type of data source you will be merging. 2. Choose type of merge - you have a choice of two types of merge, Join or Concatenate. To add new fields (columns) to your data from a separate Merge File, choose Join. To add files with duplicate column names together, for example to combine multiple regional report files to form a single global report, choose Concatenate. (see examples below) 3. Select fields to merge from external data - the drop-down list will show all the columns in the external merge file.  Untick the box next to any columns that you do not wish to import into your new, merged file. Note: if new columns might in future be added to your merge file, be sure those fields are selected, or take all fields and delete unwanted fields from the merged file. 4. Join by matching records where - if you choose Join, you must specify one or more fields containing the same data in both your file and the merge file whose columns you wish to import. Choose the field in your data set, and select the corresponding field in the external merge file.  IMPORTANT: fields being used for merge criteria may have duplicate values in one file, but not in both files. Before performing a merge, always check the uniqueness of the fields (see below) If you chose Concatenate, all the Join options will be greyed out. Scroll to the bottom and... 5. Execute Merge - for Joins, the wizard provides feedback on the matching process, reporting records in your current data not matching any incoming merge file records, exact matches and external records that do match any records in your starting file. Press [Execute merge] 6. Which record sets do you want to retain? for Joins, you can keep your non-matching records, the matching records, and/or the external merge file records that do not match as well. For Concatenates, if the file being added contains fields (columns) that are not in your original file, Omniscope will inform you you that new column(s) have been added and colour their headers blue.

Note: If you use Date and Time fields as Join criteria, the names of the fields (columns) can be different but be sure that the format of the data in both fields is exactly the same.

SUGGESTION: Before using a field (column) as a join criteria or matching field, it is good practise to check whether the values in the matching fields in both your starting file and the incoming merge file are unique (using Table View: View Tools > Tools > Select duplicate records). If both columns are unique, for a Join the number of merged records will be the same as the number of matching records, plus any non-matching records if you choose to keep them.  If your starting file's matching field has duplicate records (such as multiple addresses with the same post code), Omniscope will warn you that permutations (additional records) will be created in the resulting merged file so that all address records receive their map coordinates. If you attempt to merge two files, both of which have duplicate values in the matching fields, try to specify as many additional join criteria as you can so as to minimise the number of permutations created. 

7. If there are conflicting field names...- duplicate column names are normal for Concatenations, but before accepting the results of a Join, you must choose how to treat duplicate column names, i.e fields with exactly the same name in both files. There are two options:

Add new fields with changed names - in the event of duplicate column names, Omniscope will notify you that new columns are being added and modify the names of duplicated columns by adding a (2). See example below.

Update existing values - if you choose this options all values in columns of the starting file will be overwritten by the values in the incoming file if the column names are exactly the same. If you choose this option, ensure that the data in the incoming file always supersedes the data in the starting file. See example below:

To finalise the merge, Click OK, Apply Merge to modify your file by adding in the corresponding column values from the Merge File.  

WARNING: Before starting to merge, it is good practise to protect your source files from accidental overwriting by saving then with the 'Warn on save' options ticked. If you click File > Save (or Ctrl+S) after merging, your original data file will be overwritten with the merged file. If you want to preserve your original data file (recommended) be sure to use File > Save As to save the merged file as a new file with a different name.

Example Data Files:

Demo files used in this section are available for download here:
 MergeDemoOne, MergeDemoTwo, Concatenated, JoinedAddNew,  JoinedOverwritten

Concatenation

Concatenating the example data files aligns all the values in columns with identical names, but not in columns with different names. Notice how in the combined file, the values in Merge field file 1 are in a different column from the values in Merge field file 2. If these values were in fact comparable, you could use cut-and-paste to move the values from the second column to the first a delete the second column.

Join adding new fields with changed names

Join updating the shared values in the current file with values in the incoming file

Joining the data files above updating the values in the current file (Source One) with the values in the incoming or external file (Source Two) overwriting the values in the current file.