Join tables

Concept Information

JOIN command

Using a common key field from each table, you can join two Analytics tables with different record structures into a new third table. The fields contained in the third table can be any combination of fields from the two original tables.

Note

Carefully identify the primary and secondary tables in a join because results can differ if you reverse the order. For more information, see Common uses of joining or relating.

Steps

Note

Detailed information appears after the steps. See Join dialog box options.

  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 > Join.
  3. On the Main tab:
    1. Select the join type.

      Join types are explained below.

    2. Select the primary key field from the Primary Keys list.
    3. Select the secondary key field from the Secondary Keys list.
    4. Select the fields to include in the joined table from the Primary Fields and Secondary Fields lists.

      Note

      You must explicitly select the primary and secondary key fields if you want to include them in the joined table.

      Tip

      You can Ctrl+click to select multiple non-adjacent fields, and Shift+click to select multiple adjacent fields.

  4. In the To text box, specify the name of the new, joined table.
  5. (Optional) On the More tab:
    1. If you want to process only a subset of records, select one of the options in the Scope panel.
    2. If you want to append (add) the output results to the end of an existing Analytics table, select Append To Existing File.
  6. Click OK.

    The new, joined table is output.

Join dialog box options

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

Main tab

Options – Join dialog box Description
Join Types

Specifies which Analytics join type to use.

For detailed information, see Which records are included in the joined table?

Matched primary and secondary

(first secondary match)

The joined output table contains:

  • all matched primary records and the first matched secondary record

Matched primary and secondary

(all secondary matches)

The joined output table contains:

  • all matched primary records and all matched secondary records
  • one record for each match between the primary and secondary tables

Unmatched primary

The joined output table contains:

  • unmatched primary records

All primary and matched secondary

The joined output table contains:

  • all primary records (matched and unmatched) and the first matched secondary record

All secondary and matched primary

The joined output table contains:

  • all secondary records (matched and unmatched) and all matched primary records

    Only the first instance of any duplicate secondary matches is joined to a primary record.

All primary and secondary

The joined output table contains:

  • all primary and all secondary records, matched and unmatched

    Only the first instance of any duplicate secondary matches is joined to a primary record.
Secondary Table An alternate method for selecting the secondary table.
Primary Keys

Secondary Keys

Specifies the common key field to use to join 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 The key fields can be any data type, but they must be the same data type as each other.

    The one exception is that character and numeric key fields can be joined to each other.

  • Datetime subtypes Datetime subtypes (date, datetime, and time) can only be joined to the same subtype.
  • Length
    • It is recommended that numeric key fields be the same length.
    • If character key fields are not the same length, they are automatically harmonized.
    • Datetime key fields do not need to be the same length.
  • Names and start positions Key field names and start positions can be different, but they must describe the same data element.
  • Multiple key fields If required, the common key can include more than one key field per table. For more information, see Using multiple key fields.
Primary Fields

Secondary Fields

Specifies the fields to include in the joined table.
  • You can select fields directly in the Primary Fields and Secondary Fields lists.
  • You can also click Primary Fields or Secondary Fields to open the Selected Fields dialog box where you can select the fields, or create an expression on one or more primary fields.
  • The order in which you select primary and secondary fields dictates the field order in the resulting joined table.

    As a group, the primary fields appear before the secondary fields in the joined table.
Presort Primary Table

Presort Secondary Table

Sorts the primary or secondary tables by their key field, or fields.
  • If one or both key fields are already appropriately sorted or indexed, you can deselect Presort.
  • Presorting increases the length of time it takes to join tables, so you should use this feature only if you need to.
  • The secondary key field must be sorted or indexed in ascending order.
Local

If you are connected to a server table, specifies where to save the joined 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.
  • For most Analytics join types, the condition can reference the primary table only.
  • For the join type Matched primary and secondary (all secondary matches), the condition can reference either the primary or the secondary table, or both.

    Note

    To access the secondary table fields in the Expression Builder, select the secondary table in the From Table dropdown list.

    The If condition is evaluated against only the records remaining in a table after any scope options have been applied (First, Next, While).
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.

Note

Analytics table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.

More tab

Options – Join 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. Record processing stops as soon as one limit is reached.

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 you are joining character and numeric key fields to each other, or joining character key fields of different lengths, a message appears stating that Analytics will attempt to harmonize the fields.
  • 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.