The key fields are the common elements that allow the data from two tables to be joined. The key fields in both primary and secondary tables (fields or expressions) must be identical in generic data type and structure.
Sometimes it is necessary to modify or normalize the contents of a field before it can be used as a key field. You can use Analyzer functions in the Expression Builder to convert fields to the same generic data type, combine two fields into one, shorten fields or modify their structures (like case). See Expression Builder for more details.
When considering fields to use as your key fields, examine the fields for compatibility:
| • | Length - are character keys the same length. If not, consider using functions like SUBSTRING() in a computed field to adjust the length if reasonable. |
| • | Case - are character keys the same case - the Join command is case sensitive when comparing character keys. If not, consider using functions like UPPER() or LOWER() in a computed field to adjust the case if reasonable. |
| • | Justification - is data justified the same way in both character keys. If not, consider using functions like LTRIM() or ALLTRIM() in a computed field to adjust the justification if reasonable. |
| • | Data Type - are keys the same type of data (character, numeric, date). If not, consider using functions like VALUE() or STRING() in a computed field to adjust the data type if reasonable. |
Note: Altering your Join key data requires careful consideration - adjusting the length, case, justification or data type could fundamentally change your Join results. Only make alterations to key fields that are reasonable for the keys and that maintain the integrity of the underlying data.