Automatic harmonization when joining tables

When you join two tables, ACL automatically harmonizes the key fields in two situations:

This feature simplifies certain kinds of joins and reduces the associated labor. For example, when using key fields such as social security number or telephone number, when one key field contains numbers and punctuation formatted as character data (555-44-3322), and the other contains only numbers formatted as numeric data (555443322), you can perform a standard join without needing to first manually harmonize the fields using functions.

Whenever ACL automatically harmonizes key fields, the action and the associated syntax are recorded in the command log.

Automatic harmonization of character-numeric key field joins

If you use a character and a numeric key field to join tables, ACL automatically harmonizes the data type by using the VALUE( ) function to convert the character field to numeric while performing the join. Any alpha characters or punctuation marks such as hyphens and parentheses in the character field are ignored, and only the numbers are considered when matching values in the numeric field. The position of alpha characters has no effect on the numeric matching. The character field retains its original data type and all its characters, including alpha and punctuation, in the resulting joined table. It does not matter which field – character or numeric – is the primary key field.

Neither the character field, nor the numeric characters in the character field, need to be the same length as the numeric field. Regardless of field length, only numbers that are identical are matched.

Automatic harmonization of character and numeric key fields does not directly support the matching of negative numbers. To account for some intended behavior of the VALUE( ) function, ACL also uses the ABS( ) function on the character key field, which temporarily converts all numeric values to positive while the join is being performing. If you want to use automatic harmonization with key fields that include negative numbers, perform the join in the usual manner, and then re-run the join using the command log entry manually edited to apply only the VALUE( ) function to the character key field. The same modification applies to scripting syntax. This method produces correct results when joining character and numeric key fields that include negative values, but it does not work reliably if any non-numeric data exists in either field.

Automatic harmonization of character key field length

If you select character key fields of different lengths, ACL automatically harmonizes their length by adding blanks to the shorter field while performing the join. The shorter field retains its original length in the resulting joined table. Only character key fields are automatically harmonized for length. Numeric and datetime key fields are not.

Matching of values in character key fields harmonized for length still depends on an exact match between the values themselves. Shorter and longer versions of a value – for example, ‘ACL’ and ‘ACL Services Ltd’ – still do not produce a match even though during the processing of the join they are contained in fields of harmonized length.

Automatic length harmonization also works for character-based computed key fields, and common keys composed of multiple character key fields.

Related concepts
About joining tables
Types of joins
Examples of join types
Related tasks
Joining tables
Related reference
VALUE( ) function
ABS( ) function

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