Automatic harmonization when joining tables

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

  • You use one character key field and one numeric key field
  • You use character key fields of different lengths

Automatic harmonization simplifies certain kinds of joins and reduces the associated labor.

Whenever Analytics 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, Analytics automatically harmonizes the data type by using the VALUE( ) function to convert the character field to numeric while performing the join.

Example

You want to join two tables using social security number as the common key field.

  • One key field contains numbers and punctuation formatted as character data: 555-44-3322
  • The other key field contains only numbers formatted as numeric data: 555443322

Because Analytics automatically harmonizes character-numeric joins, you can perform a standard join without needing to first manually harmonize the fields using functions.

Additional details

  • 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.
  • Either the character or the numeric field can be 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 numeric values that are identical are matched.

Automatic harmonization involving negative numbers

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, Analytics 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. 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 when joining tables, Analytics automatically harmonizes their length by adding blanks to the shorter field. The shorter field retains its original length in the resulting joined table.

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

Only character key fields are automatically harmonized for length. Numeric and datetime key fields are not.

Matching of values not affected by harmonization

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, ‘ABC’ and ‘ABC Corporation’ – still do not produce a match even though during the processing of the join they are contained in fields of harmonized length.