Merging tables

Merging tables allows you to combine two sorted Analytics tables with identical record structures into a new third table that preserves the sort order of the original tables. Merging works by interfiling records, which means to combine records in accordance with their existing sort order.

You can use the merge operation to perform useful tasks such as combining sorted employee tables into a unified table that preserves the sort order.

Tables require an identical record structure

In order for two tables to be successfully merged, the records in both tables must be exactly identical in structure:

• The data elements, and the number and order of fields, must be identical.
• The data type of corresponding fields must be identical.
• The start position and length of corresponding fields must be identical.
• For datetime fields, the datetime format must be identical.

Compare the record structure

Before you attempt to merge two tables, you can compare the corresponding fields in the tables to ensure they have an identical structure. For more information, see Comparing data structures.

If a difference in data structure at the field level is preventing successfully merging tables, you may be able to harmonize the corresponding fields. For more information, see Harmonizing fields.

Merge tables using a common key field

You merge tables using a common key field – that is, a data element such as employee number, vendor ID, or last name, that appears in both tables. Records in the two original tables are positioned in the merged table based on their place in the sort order used by the original tables.

Several requirements apply to the key fields in the tables you are merging:

Key field characteristic	Requirement
Data element	Must be the same. For example, both key fields are last name fields.
Sort order	Must be the same, and must be ascending. Note You can use the Presort Primary Table option for sorting the primary key field during the merge operation. If the secondary key field is unsorted, you must first sort it in a separate sort operation before performing the merge.
Data type	Must be character.
Field type	Can be physical fields or computed fields.
Field name	Can be different.
Start position	Must be the same.
Field length	Must be the same.

Primary and secondary tables and key fields

The tables and the key fields in the merge operation are identified as primary and secondary based on the order in which you open the tables:

• primary table – the first table you open

• primary key field – the key field you choose from the primary table

• secondary table – the second table you open

  Opening a secondary table means associating it with a primary table and making it available for processing. Secondary tables are not opened in the View tab.

• secondary key field – the key field you choose from the secondary table

You are free to choose whatever primary and secondary tables and key fields you want. However, the merge will succeed only if the tables and key fields meet the requirements for merging.

For more information, see About key fields.

Merging tables using multiple key fields

If you want to merge two tables using more than one primary and secondary key field (that is, more than one common key), these additional requirements apply:

• All key fields must be sorted in ascending order, which means there must be a nested sorting pattern in each table.
• The order in which you select the key fields in each table must be the same as the order of the nested sorting pattern in each table.
• Both tables must use the same nested sorting pattern.

Additional information about merging

The table below provides additional information about merging.

Functional area	Details
Size of output table	The number of records in the resulting combined table is the sum of the records in the two tables being merged.
Records versus fields	You can merge entire records only.
Data type of key fields	Only character fields, or character computed fields, can be key fields. Tip You can use an Analytics function to convert a numeric or datetime field to a character field. For more information, see Harmonizing fields.
Identical key field values	When key field values are identical in primary and secondary table records, primary table records are sorted above secondary table records.
Names of corresponding fields	Corresponding fields in the primary and secondary tables do not need identical names. In the resulting combined table, the primary table field names take precedence.
Corresponding computed fields	It there are corresponding computed fields, the expression in the computed field in the primary table takes precedence over the expression in the secondary table.
Performance tip	When merging two tables of different sizes, using the larger table as the primary table requires less processing.
Indexing instead of sorting	The primary and secondary key fields can be indexed in ascending order instead of sorted. Indexing can provide performance benefits over sorting. Applying an index to the secondary table can only be performed from the command line or in a script.
Scope parameters	The If, While, First, and Next parameters that limit which records are processed apply only to the primary table.
Location of tables	In order to be merged, tables must be in the same Analytics project. Server tables must be on the same server, and must be accessed using the same server profile. You cannot merge a local table with a server table.