Relate tables

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.

Steps

1. In the Navigator, open the parent table.
2. Select Data > Relate.
3. 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.

4. 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.

5. 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.

6. 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.

7. (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.

8. 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. You select the common key field by dragging key field to key field. Once the key field arrow is in place, you can right click it and select Edit Relation to change the common key field. Key field guidelines: Data type The key fields can be any data type. For each table pair, key fields must be the same data type. Datetime subtypes Datetime subtypes (date, datetime, and time) can only be related to the same subtype. Length It is recommended that key field lengths be identical for each table pair. Names and start positions Key field names and start positions can be different, but they must describe the same data element. Multiple key fields If required, the common key can include more than one key field per table. For more information, see Using multiple key fields.
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: Right-click when you drag the key field from the parent table to the child table. Select Relate using Named Index. Relate using Named Index is disabled if an index already exists. Specify a name for the index, and if desired a location other than the default (the folder containing the Analytics project) Click OK.
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.

1. Right-click the parent view and select Add Columns.
2. 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.

3. 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.

4. 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.
5. 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.