About joining tables

Joining allows you to combine two sorted ACL tables with different record structures into a new third table that can contain any combination of fields from the two original tables (Figure 1). Record structures are different if they have one or more data elements that differ. Joining is a good choice for investigative work that requires a permanently joined set of data as a starting point for analysis.

Figure 1. Joining tables

Requirements when joining tables

To be joined, the two tables require a common key field – that is, a data element such as employee number or vendor ID that appears in both tables. When identical values exist in the key fields, the result is a match that joins individual records from the separate tables. Partial matching is not supported by the basic join operation. Matched and unmatched key field values, and the type of join you perform, determine which records from the two original tables are included in the third table.

The key fields can be any data type, or computed fields, but they must be the same data type as each other – with the exception of character-numeric and numeric-character data type joins, which ACL automatically harmonizes. Datetime subtypes (date, datetime, and time) can only be joined to the same subtype. ACL also automatically harmonizes the length of character key fields. Justification and case must be the same. The key fields do not need identical names, or the same start position, but they must describe the same data element.

Primary and secondary tables and key fields

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

You are free to choose whatever primary and secondary tables and key fields you want. (For more information, see About key fields.) However, the join will succeed only if the tables and fields meet the requirements for joining.

Sorting of joined tables

The combined table resulting from a join is sorted in ascending order on the primary key field, assuming you Presort the primary key field while performing the join, or the primary table already uses this sort order. If you do not sort or presort by the primary key field, the resulting joined table uses the existing sort order of the primary table.

Additional information about joining

Table 1 provides additional information about joining.

Table 1. Additional information about joining

Functional area

Details

Record matching

Of the six join types that ACL supports, five are many-to-one joins. Many-to-one joins match primary key values to the first occurrence only of a matching secondary key value. If additional occurrences of a matching secondary key value exist, they are ignored, or treated as an unmatched record, depending on the type of join. The sixth type of ACL join, the many-to-many join, matches primary key values to every occurrence of a matching secondary key value.

Unmatched records and missing field values

If you include unmatched primary or unmatched secondary records in a join, for the missing field values, ACL displays a blank in character and datetime fields, a zero in numeric fields, and “F” in logical fields.

Duplicates or blanks in secondary table key field

If duplicates or missing values in a secondary table key field render subsequent analysis invalid, pre-processing the secondary table to remove the duplicates and/or blanks may be a solution in some circumstances.

Identical key field length not enforced

With the exception of character key fields, ACL does not enforce identical lengths for the primary and secondary key fields when joining tables. However, it is recommended that you always use identical lengths for numeric key fields, manually harmonizing the lengths prior to performing the join, if necessary. Results derived from joining using numeric key fields of different lengths are not reliable.

Datetime key fields can be different lengths because ACL, when performing operations involving dates, datetimes, or times, uses an internal ACL datetime format.

Harmonizing justification and case

When you join tables using character key fields, justification and case must be the same:

  • Both key fields must have the same justification. Use the LTRIM( ) function to remove leading blanks from the key fields.

  • Both key fields must be in the same case – UPPER, lower, or Proper. To harmonize the case, use the UPPER( ), LOWER( ), or PROPER( ) function.

Sorting and indexing

The primary and secondary key fields can be indexed instead of sorted. The secondary key field must be indexed in ascending order. Applying an index to the secondary table can only be performed from the command line or in a script.

It is not mandatory that the primary table be sorted or indexed, but processing time increases significantly if the primary table is completely unsorted, or sorted in descending order. If you perform a join using a partially sorted primary table key field – for example, joining on account code when the table is sorted by month and then by account code – the increase in processing time is not as significant.

When joining, the Presort option exists for both the primary and the secondary tables.

If you perform a join using an unsorted primary table, the existing order of the primary table records is maintained in the joined table.

Count of records not included in a join

Depending on the type of join you perform, records in the primary and/or secondary tables may not be included in the joined table. The command log displays the number of primary records not included (<n> records bypassed), but not the number of bypassed secondary records.

Conditional expressions and scope options used in join operation

In many-to-one joins, the If, While, First, and Next parameters that limit which records are processed apply only to the primary table. In many-to-many joins, If and While expressions can also reference the secondary table.

Identical field names in tables being joined

If the primary and secondary key fields, or any other included fields, have identical names, ACL adds ‘2’ to the end of the secondary field name in the layout for the output table. For example, ‘vendor_ID’ becomes ‘vendor_ID2’ (or ‘vendor_ID3’, and so on, until ACL finds a name that does not conflict with any other field names in the output table). The alternate column titles in the view for the output table continue to display the identical names unaltered.

Table unavailable as secondary table

A table is unavailable to select as the secondary table in a join if it is currently related to the primary/parent table as a child table. To avoid this restriction, you can create a copy of the primary/parent table layout, or the child table layout, and join using the copied layout, or you can delete the relation.

Restrictions on location of tables being joined

To be joined, tables must be in the same ACL project. Server tables must be on the same server, and must be accessed using the same server profile. You cannot join a local table with a server table.

Size of joined table

Depending on the type of join performed, the number of records in the resulting combined table can be greater than, equal to, or less than the sum of the records in the two tables being joined.

Joining UTC-based and non-UTC data

A UTC-based and a non-UTC datetime key field can be used to join two tables. (UTC is Coordinated Universal Time, the time at zero degrees longitude.) When performing operations involving datetimes or times, ACL uses an internal ACL datetime format, so the following two datetimes are interpreted as identical, and constitute a match:

  • UTC-based – 31/12/2014 10:30:15-05:00

  • non-UTC – 31/12/2014 15:30:15

You should exercise caution if you mix UTC-based and non-UTC time data in an ACL operation. Although ACL will match the two time values above, it may not make logical sense to do so, because one value references a time zone, and the other value does not. For more information about UTC, see Options dialog box: Date and Time tab.

Related concepts
Types of joins
Examples of join types
Automatic harmonization when joining tables
Joining versus relating
Common uses of joining or relating
About relating tables
Using multiple key fields
About key fields
Combining data
Data structure and data format requirements
Harmonizing fields
Related tasks
Joining tables


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback