Using multiple key fields
Two different situations can require that you use multiple common key fields to accurately join or relate tables:
| Use... | When... | Example |
|---|---|---|
| Multiple key fields in combination | The values in a single common key field are insufficiently unique to accurately join or relate two tables. | You need to use both the vendor ID field and the Location field to accurate join or relate two tables. |
| Multiple key fields in isolation | The values required to join or relate two tables are split between two (or more) key fields in one of the tables being joined or related. | You are joining or relating tables on Name. The primary or parent table contains a single Name field. However, names can occur in either of two fields in the secondary or child table. |
Using multiple key fields in combination
If the values in a single common key field are insufficiently unique to accurately join or relate two tables, you need to use multiple common key fields in combination.
Example
You want to join or relate two tables using Vendor_ID as a common key field. However, some vendors have multiple locations for the same vendor ID.
In this example, Vendor A4538 has locations in Vancouver, Richmond, and Coquitlam.
Single key field
If you join or relate tables using only Vendor_ID, secondary or child table records with anything other than the first listed location for the vendor are not included in the joined table (assuming a many-to-one join), or the related table, and locations are incorrectly matched between tables.
Multiple key fields in combination
To capture all vendor locations, and ensure proper location matching, you need to use both the Vendor_ID and the Location fields as key fields in both tables. When combined, the values in each field form a single unique value that can be used to reliably match records between the two tables.
Specifying multiple key fields in combination when joining tables
When joining tables, you can use either of these methods to specify multiple key fields in combination:
- Select more than one key field in the Join dialog box.
- In each table, create a computed field that concatenates (adds together) two or more key fields, and join the tables using the computed fields. For more information, see Concatenate key fields.
Select more than one key field in the Join dialog box
When you select more than one key field for each table in the Join dialog box, the following conditions apply:
| Data structure | The data structure and data format requirements that apply when using one key field still apply to the corresponding key fields in each table when using multiple key fields. For more information, see Data structure and data format requirements. |
|---|---|
| Data type | Within a table, the multiple key fields can be of different data types – for example, first name, last name, and date of birth. |
| Sort order | Selecting more than one key field creates a nested sort order in the output table, assuming you Presort the primary table while performing the join. The order in which you select the key fields dictates the priority of the nested sort order. |
Specifying multiple key fields in combination when relating tables
When relating tables, you can use either of these methods to specify multiple key fields in combination:
- In each table, create a computed field that concatenates (adds together) two or more key fields, and relate the tables using the computed fields. For more information, see Concatenate key fields.
- In each table, define a new field long enough to encompass the data in the multiple key fields, and relate the tables using the new field. For more information, see Define a physical field.
Note
Unlike joining, when you relate tables you can select only one key field per table pair, so you need to employ one of the methods above to use multiple key fields in combination.
Define a new field to encompass data in multiple key fields
When you define a new field to encompass data in multiple key fields, the following conditions apply:
| Data structure | The data structure and data format requirements that apply when using one key field still apply to newly created fields that encompass multiple key fields. For more information, see Data structure and data format requirements. |
|---|---|
| Field adjacency | This method works only if the multiple key fields are adjacent in each table. Fields can be made adjacent by extracting by field to a new table and selecting the fields for extraction in the required order. |
| Data type |
New fields that encompass multiple key fields can be any data type supported by the source data. If the multiple key fields are of different data types, you can create the new field encompassing them as a character field because you are using it only for the purposes of relating tables. |
Using multiple key fields in isolation
If the values required to accurately join or relate two tables are contained in two (or more) key fields in one of the tables being joined or related, you need to use these multiple key fields in isolation.
For each of the multiple key fields you perform a separate join operation, or form a separate relational association, in a process that yields a unified final result with a complete set of data.
If you join or relate the tables using only one of the key fields in the tables with multiple key fields, the resulting data is incomplete.
Example 1: Two key fields in the secondary or child table
You want to use names to join or relate two tables. The primary or parent table contains the Name field. However, the secondary or child table contains two different name fields – Name_1 and Name_2. Matching names in the secondary or child table could appear in either of the two name fields.
Joining tables
To capture all possible matches between names you need to perform two successive joins, with each join using only one of the key fields in the secondary table. You use the output table from the first join as the primary table in the second join.
With each join you must select the join type that includes matched and unmatched primary records (that is, all primary records) so you do not lose the unmatched primary records at any point in the process.
Note
The figures below illustrate only the key fields in the tables being joined. Typically, tables also include other data in non-key fields.
Relating tables
To capture all possible matches between names you need to add an additional instance of the child table for the additional relation between the parent key field and the second child key field.
You add additional instances of the child table by clicking the Add Table button in the Relations dialog box and selecting the appropriate table.
Example 2: Two key fields in the primary or parent table
You want to use a tax filer ID number to join or relate two tables. The primary or parent table contains tax return information, and the secondary or child table maps the tax filer ID number to social security numbers.
The primary or parent table contains the ID field with ID numbers of the main filers, and the Secondary_ID field with ID numbers of the secondary filers (spouses), when applicable. The secondary or child table contains one ID field with ID numbers for everyone. You want to produce joined or related data that associates social security numbers to both main and secondary tax filers.
Joining tables
To associate social security numbers to both main and secondary tax filers you need to perform two successive joins, with each join using only one of the key fields in the primary table. You use the output table from the first join as the primary table in the second join.
With each join you must select the join type that includes matched and unmatched primary records (that is, all primary records) so you do not lose the unmatched primary records at any point in the process.
Note
The figures below illustrate only the key fields in the tables being joined. Typically, tables also include other data in non-key fields.
Relating tables
To associate social security numbers to both main and secondary tax filers you need to add an additional instance of the child table for the relation between the second parent key field and the child key field.
You add additional instances of the child table by clicking the Add Table button in the Relations dialog box and selecting the appropriate table.
Analytics 14.1 Help