Merge tables

Concept Information

MERGE command

Using a common key field from each table, you can merge two sorted Analytics tables with identical record structures into a new third table that uses the same sort order as the original tables.

Note

To successfully merge tables, the data in both tables must be exactly identical in structure. For more information, see Merging tables.

Steps

  1. In the Navigator, open the primary table, and right-click the secondary table and select Open as Secondary.

    The primary and secondary table icons update with the numbers 1 and 2 to indicate their relation to each other    .

  2. Select Data > Merge.
  3. On the Main tab:
    1. Select the primary key field from the Primary Keys list.
    2. Select the secondary key field from the Secondary Keys list.
  4. In the To text box, specify the name of the new, merged table.
  5. On the More tab:
    1. (Optional) To specify that only a subset of records are processed, select one of the options in the Scope panel.
    2. Click OK.

Merge dialog box options

The tables below provide detailed information about the options in the Merge dialog box.

Main tab

Options – Merge dialog box Description
Secondary Table An alternate method for selecting the secondary table.

Primary Keys

Secondary Keys

 Specifies the common key field to use to merge the two tables.
  • You can select the common key field directly in the Primary Keys and Secondary Keys lists.
  • You can also click Primary Keys or Secondary Keys to open the Selected Fields dialog box where you can select the common key field, or create an expression on the primary key.

Key field guidelines:

  • Data type Both key fields must be character fields.
  • Data structure The following elements must be exactly identical for both key fields:
    • start position
    • field length
  • Sorting Both key fields must be sorted in ascending order.
  • Multiple key fields If required, the common key can include more than one key field per table. For more information, see Merging tables using multiple key fields.

Presort Primary Table

 Sorts the primary table by the key field, or fields.
  • If the key field or fields are already appropriately sorted or indexed, you can deselect Presort.
  • Presorting increases the length of time it takes to merge tables, so you should use this feature only if you need to.
  • The secondary key field must already be sorted or indexed in ascending order because there is no Presort option for the secondary key field.
Local

If you are connected to a server table, specifies where to save the merged table.

  • Local selected saves the output table to the same location as the Analytics project, or to a specified path, or location you navigate to.
  • Local deselected saves the output table to the Prefix folder on AX Server.
Use Output Table Specifies whether the Analytics table containing the output results opens automatically upon completion of the operation.
If

(Optional) Allows you to create a condition to exclude records from processing.

  • You can enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.
  • The condition applies only to the primary table.
To Specifies the name and location of the output table.
  • To save the output table to the Analytics project folder enter only the table name.
  • To save the output table in a location other than the project folder specify an absolute or relative file path, or click To and navigate to a different folder.

    For example: C:\Results\Output.fil or Results\Output.fil.

Regardless of where you save the output table, it is added to the open project if it is not already in the project.

If Analytics prefills a table name, you can accept the prefilled name, or change it.

More tab

Options – Merge dialog box Description
Scope panel Specifies which records in the primary table are processed:
  • All (default) all records in the primary table are processed.
  • First select this option and enter a number in the text box to start processing at the first record in the primary table and include only the specified number of records.
  • Next select this option and enter a number in the text box to start processing at the currently selected record in the primary table view and include only the specified number of records.

    The actual record number in the leftmost column must be selected, not data in the row.

  • While select this option to use a WHILE statement to limit the processing of records in the primary table based on criteria.
    • You can enter a condition in the While text box, or click While to create a WHILE statement using the Expression Builder.
    • A WHILE statement allows records to be processed only while the specified condition evaluates to true.
    • You can use the While option in conjunction with the All, First, or Next options.

Note

The number of records specified in the First or Next options references either the physical or the indexed order of records in a table, and disregards any filtering or quick sorting applied to the view. However, results of analytical operations respect any filtering.

If a view is quick sorted, Next behaves like First.
Append To Existing File Specifies that the output results are appended (added) to the end of an existing Analytics table.

Note

Leaving Append To Existing File deselected is recommended if you are uncertain whether the output results and the existing table have an identical data structure.

For more information about appending and data structure, see Appending output results to an existing table.
OK Executes the operation.

If the overwrite prompt appears, select the appropriate option.

If you are expecting the Append option to appear and it does not, click No to cancel the operation and see Appending output results to an existing table.