Relating tables

Concept Information

Relating tables allows you to combine up to 18 Analytics tables with different record structures and access and analyze data from any combination of fields in the related tables as if they existed in a single table.

Record structures are different if they have one or more fields (data elements) that differ. Relating is a good choice for informational work that requires a quick picture of data associations across several physical tables, or for linking codes with corresponding full names prior to generating reports.

Example

Scenario

You want to create a sales report that contains details about the customers, and the products sold, for the month of March, but the data is spread across three tables.

Approach

You relate the Customer master table with the Orders table, and the Orders table with the Product master table, to create a temporary association of tables that contains all the information you need for the report:

customer name and location from the Customer master table
order details from the Orders table
product details from the Product master table

Relating creates a "virtual" table

The result of relating tables is virtual – the related tables remain independent, and can be unrelated at any time.

Unlike joining or merging, relating does not create a new table. Instead, the fields of the related or child table(s) become available in the parent table from which the relation was created.

If required, you can perform a separate operation and extract any combination of fields from the parent and child tables to a new table.

Relate tables using a common key field

You relate tables using a common key field – that is, a data element such as employee number, vendor ID, or address, that appears in both tables. When identical values exist in the key fields, the result is a match that relates individual records from the separate tables. Partial matching is not supported by the basic relation operation.

Several requirements apply to the key fields in the tables you are relating:

Key field characteristic	Requirement
Data element	Must be the same. For example, both key fields are employee number fields.
Data type	Can be any data type, but key fields must be the same data type as each other. For example, two character fields. Datetime subtypes (date, datetime, and time) can only be related to the same subtype.
Field type	Can be physical fields or computed fields.
Field name	Can be different.
Start position	Can be different.
Field length	Must be the same.
Justification and case in character fields	Must be the same.

Parent and child tables and key fields

The tables and key fields in the relation operation are identified as parent and child based on the order in which you add the tables to the relation:

parent table – the first table you add (automatically added when you open a table and begin the relation operation)
parent key field – the key field you choose from the parent table
child table – the second table you add, and any subsequent tables you add
child key field – the key field you choose from the child table, or tables

You are free to choose whatever parent and child tables and key fields you want. However, the relation will succeed only if the key fields meet the requirements for relating.

For more information, see About key fields.

Accessing child table fields

Once a relation is established, you can add fields from any of the child tables to the parent view, although there is no requirement that you do so. You can access and analyze child table fields through the parent table – using the From Table drop-down list in Analytics dialog boxes and the Expression Builder – whether or not you have added them to the parent view.

Child table fields accessed through the parent table are displayed in table name.field name format to indicate which table the fields are from. You can access related tables, and modify relations, only through the parent table, not through a child table.

Sorting and indexing of related tables

The virtual table resulting from a relation uses the existing sort order of the parent table. You do not have to sort or index the parent table key field prior to relating tables. However, you might choose to do so, because there is no Presort option available for the parent table during a relation.

As part of the internal functioning of the relation operation, the child table key field(s) are automatically indexed in ascending order. These child table indexes persist even after the child tables are unrelated, and can be manually deleted, if necessary.

Additional information about relating

The following table provides additional information about relating.

Functional area	Details
Record matching	Relating a table pair is the logical equivalent of joining them using the All Primary Records option – that is, using the type of many-to-one join that includes matching primary and secondary records (parent and child records), and unmatched primary records. As with the corresponding many-to-one join, the relating operation matches parent key values to the first occurrence only of a matching child key value. If additional occurrences of a matching child key value exist, they are ignored. You need to take this behavior into account when planning your table relations, especially if a child table contains legitimate multiple occurrences of a matching key value. One approach is to try reversing the relation of the two tables, making the child the parent, and vice versa.
Unmatched records and missing field values	If a parent key value has no match in a related child table, for the missing field values, Analytics displays a blank in character and datetime fields, a zero in numeric fields, and “F” in logical fields.
Duplicates or blanks in child table key field	If duplicates or missing values in a child table key field render subsequent analysis invalid, pre-processing the child table to remove the duplicates and/or blanks may be a solution in some circumstances.
Extracting data from related tables	You have two options when extracting data from related tables: Using either the View or Fields option in the Extract dialog box, you can extract some or all of the data from the parent and child tables into a new Analytics table. The new table is no longer related to any other tables. If you use the View option, you must first add the pertinent child table data to the parent view. Using the Record option in the Extract dialog box, you can extract the data from the parent table into a new Analytics table. The new table retains the relations of the original parent table. The Record option does not support extracting child table data.
Identical key field length not enforced	Analytics does not enforce identical lengths for the common key fields in parent and child tables. It is recommended that you always use fields of identical length, manually harmonizing the lengths prior to performing the relation, if necessary. Results derived from relating using key fields of different lengths are not reliable. Datetime key fields can be different lengths because Analytics, when performing operations involving dates, datetimes, or times, uses an internal Analytics datetime format.
Changing key field data type	You cannot change the data type of a parent or child key field while it is being used to relate tables. If you need to change the data type of either field, you must first delete the relation. If the change results in the data types being different from each other, you are no longer able to use the two fields to relate tables.
Avoiding conditional indexing	Do not use a conditional index for child table key fields. Instead, apply conditions when you perform operations against a parent table and its related table(s). Using conditional indexes when building relations can cause unintended data gaps at different points in a relational hierarchy. The safer approach is to build relations that present as full a data set as the relations warrant, and subsequently apply conditions, if required.
Restrictions on location of tables being related	To be related, tables must be in the same Analytics project. Server tables must be on the same server, and must be accessed using the same server profile. You cannot relate a local table and a server table.
Harmonizing justification and case	When you relate table pairs using character key fields, justification and case must be the same: Both key fields must have the same justification. Use the LTRIM( ) function to remove leading blanks from the key fields. Both key fields must be in the same case – UPPER, lower, or Proper. To harmonize the case, use the UPPER( ), LOWER( ), or PROPER( ) function.
Relating UTC-based and non-UTC data	A UTC-based and a non-UTC datetime key field can be used to relate two tables. (UTC is Coordinated Universal Time, the time at zero degrees longitude.) When performing operations involving datetimes or times, Analytics uses an internal Analytics datetime format, so the following two datetimes are interpreted as identical, and constitute a match: UTC-based – 31/12/2014 10:30:15-05:00 non-UTC – 31/12/2014 15:30:15 You should exercise caution if you mix UTC-based and non-UTC time data in an Analytics operation. Although Analytics will match the two time values above, it may not make logical sense to do so, because one value references a time zone, and the other value does not. For more information about UTC, see Date and Time options.