Joining tables

Using a common key field from each table, you can join two sorted ACL 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 your primary and secondary tables because results can differ if you reverse the order. For more information, see Common uses of joining or relating.

To join tables:

  1. Do one of the following:
    • Open the primary table and select Data > Join.

    • Open the primary table, right-click the secondary table and select Open as Secondary, and select Data > Join.

  2. On the Main tab, if you have not already opened the secondary table, select it from the Secondary Table drop-down list.

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

  3. Select the common key fields by doing the following:
    1. Select the primary key field from the Primary Keys list.

      You can also click Primary Keys to select the field, or to create an expression. You are prevented from specifying a descending sort order for the primary key field.

    2. Select the secondary key field from the Secondary Keys list.

      You can also click Secondary Keys to select the field. You cannot create an expression for a secondary key field, or specify a descending sort order.

    Key field guidelines:

    • The key fields can be any data type.

    • Primary and secondary key fields must be the same data type, except for character and numeric key fields, which can be joined to each other.

    • Datetime subtypes (date, datetime, and time) can only be joined to the same subtype.

    • It is recommended that numeric key fields be the same length. Character key fields, if they are not the same length, are automatically harmonized. Datetime key fields do not need to be the same length.

    • Key field names and start positions can be different, but they must describe the same data element.

    • If required, the common key can include more than one key field per table. For more information, see Using multiple key fields.

  4. Select the fields that will be included in the joined table by doing the following:
    1. Select one or more primary fields from the Primary Fields list.

      You can also click Primary Fields to select the field(s), or to create an expression.

    2. Select one or more secondary fields from the Secondary Fields list.

      You can also click Secondary Fields to select the field(s). You cannot create an expression for a secondary field.

    Note

    Primary and secondary key fields are only included in the joined table if you also select them as primary or secondary fields.

    The order in which you select primary and secondary fields dictates the field order in the resulting joined table. The primary fields appear before the secondary fields in the joined table.

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

  5. If the primary key field is already appropriately sorted or indexed, deselect Presort Primary Table, otherwise leave it selected.
  6. If the secondary key field is already sorted or indexed in ascending order, deselect Presort Secondary Table, otherwise leave it selected.
  7. If you are connected to a server table, do one of the following:
    • Select Local to save the output table to the same location as the project, or to specify a path or navigate to a different local folder.

    • Leave Local deselected to save the output table to the Prefix folder on the ACL Server.

      Note

      For output results produced from analysis or processing of ACL Analytics Exchange server tables, select Local. You cannot use the Local setting to import results tables to ACL Analytics Exchange Server.

  8. Select or deselect Use Output Table depending on whether or not you want the ACL table containing the output results to open automatically upon completion of the operation.
  9. If there are records in the primary view that you want to exclude from processing, enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.

    The IF statement considers all records in the primary view and filters out those that do not meet the specified condition.

    Many-to-many joins also allow referencing the secondary table in IF statements. After you have specified a many-to-many join, you can select the secondary table from the From Table drop-down list in the Expression Builder.

  10. Do one of the following:
    • In the To text box, specify the name of the ACL table that will contain the output results.

    • Click To and specify the ACL table name, or select an existing table in the Save or Save File As dialog box to overwrite or append to the table.

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

    You can also specify an absolute or relative file path, or navigate to a different folder, to save or append the table in a location other than the project location. For example: C:\Results\Output.fil or Results\Output.fil. Regardless of where you save or append the table, it is added to the open project if it is not already in the project.

  11. Click the More tab.
  12. Select the appropriate option in the Scope panel.
    • All – This option is selected by default. Leave it selected to specify that all records in the primary view will be processed.

    • First – Select this option and enter a number in the text box to start processing at the first record in the primary view 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 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 view based on a particular criterion or set of 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 in the primary view to be processed only while the specified condition evaluates to true. As soon as the condition evaluates to false, the processing terminates, and no further records are considered. You can use the While option in conjunction with the All, First, or Next options.

      Many-to-many joins also allow referencing the secondary table in WHILE statements. After you have specified a many-to-many join, you can select the secondary table from the From Table drop-down list in the Expression Builder.

    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.

  13. Select the join type in the Join Categories panel.

    For more information, see Types of joins.

  14. If you want to append the output results to the end of an existing ACL table, do one of the following:
    • Select Append To Existing File if you are certain the output results and the existing table are identical in structure.

    • Leave Append To Existing File deselected if you want ACL to compare the record lengths of the output results and the existing table. If the record lengths are not identical, the data structure is not identical, and the append will not work correctly.

    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 results to ACL tables and text files.

  15. Click OK.

    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 ACL will attempt to harmonize the fields. Click OK to continue, or Cancel if you do not want ACL to harmonize the fields. You can optionally suppress future appearances of the message.

  16. 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 results to ACL tables and text files.

Related concepts
Saving results and specifying results output folders
About joining tables
Types of joins
Examples of join types
Automatic harmonization when joining tables
Using multiple key fields


(C) 2015 ACL Services Ltd. All Rights Reserved.