Relate tables
Concept Information
Using a common key field from each table pair, you can relate two or more Analytics tables with different record structures. Once tables are related, you can use the parent table to access and analyze data from any combination of fields in the related tables.
Note
Carefully identify the parent and child tables in a relation because results can differ if you reverse the order. For more information, see Common uses of joining or relating.
Steps
- In the Navigator, open the parent table.
- Select .
- In the Relations dialog box, click Add
Table and select one or more child tables.
You can relate up to 18 tables, including the parent table.
Tip
You can Ctrl+click to select multiple non-adjacent tables, and Shift+click to select multiple adjacent tables.
You can double-click a child table to add it singly.
- Click Add and then Close.
Tip
You can resize the Relations dialog box, or tables in the dialog box, and move tables, to create more room in which to work, or to make field information more visible.
- Drag the key field from the parent table to
the corresponding key field in the child table.
An arrow appears between the two key fields, indicating the relation between the tables.
Parent and child tables are related using an index on the child table key field. For more information, see Child table index.
- Relate any additional tables in the same manner as the
first table pair, by dragging key field to key field.
Each additional relation must create a direct or indirect link to the parent table.
Note
Individual instances of two tables can have only one relation. If you try to relate the same table pair a second time, the operation is prohibited and an error message appears. Add another instance of the required table by clicking the Add Table button and selecting the appropriate table.
For more information, see Using multiple key fields in isolation.
- (Optional) To
remove an individual relation, or a table, from the Relations dialog
box, do the following:
- To remove a relation right-click the key field arrow and select Delete
- To remove a table right-click the body of the table and select Remove
Table
Note
If the table has an existing relation, you must delete the relation first.
- Click Finish to exit the Relations dialog
box.
You can now access and analyze data from any combination of fields in the tables you have just related, as if all the fields existed in a single table.
Relations dialog box options
The table below provides detailed information about the options in the Relations dialog box.
Options – Relations dialog box | Description |
---|---|
Add Table | Opens the Add Table dialog box. |
Add Table dialog box | Specifies the tables to include in the relation. |
Key field arrow | Specifies the common key field to use to relate each table pair.
Key field guidelines:
|
Arrange Tables |
(Optional) You can right-click the working area of the Relations dialog box and select Arrange Tables to neaten the arrangement of tables and key field arrows. |
Finish |
Executes the operation. You can now access and analyze data from any combination of fields in the tables you have just related, as if all the fields existed in a single table. When accessed from the parent table, the From Table drop-down list in Analytics dialog boxes and the Expression Builder allows you to select related tables from which you can then select individual fields for analysis or processing. |
Child table index
Parent and child tables are related using an index on the child table key field.
If no index exists | If no index exists on the child table key field, Analytics automatically creates one when you relate the parent and child tables. |
---|---|
If you want to specifically name the index |
If you want to specifically name the child table index auto-created by Analytics:
|
If multiple indexes exist | If the child table has two or more existing indexes on its key field, you are presented with a list of these eligible indexes. Select the appropriate index and click OK |
(Optional) Add child table fields to the parent view
There is no requirement that you add child table fields to the parent view, although you may find that doing so helps you better visualize the related data.
- Right-click the parent view and select Add Columns.
- Select a child table from the From Table drop-down
list.
In the Available Fields list, child table fields appear in the format child table name.field name.
- Select one or more child table fields to add to the
parent view.
Child table fields appear in the parent view in the order in which you select them.
- If applicable, select additional child tables from the From Table drop-down list, and select additional child table fields to add to the parent view.
- Click OK.
The child table fields are added to the parent view. Analytics fills missing values in child table fields for unmatched parent table records with blanks or zeros.