Appending tables

Appending tables combines records from two or more Analytics tables into a new table. You may need to append multiple tables into a single table before you can perform analysis.

For example, you want to perform analysis on an entire year's worth of data but the data is spread among twelve monthly Excel worksheets. After importing the individual worksheets into Analytics, you can then append them to create a single annual table for analysis.

How does appending work?

Appending adds one group of records to the bottom of another group of records. The records from each source table are appended in the order in which you select the tables. The new table contains the records from the first selected table, followed by the records from the second selected table, and so on.

The source tables can have different or identical record structures, and can be sorted or unsorted.

How fields are appended

When you append records from multiple tables, how the individual fields within the records are appended depends on whether the fields have identical names or unique names.

Fields with identical names

Source table fields with identical physical names and identical data categories are directly appended to one another.

In the diagram below, fields A, B, and C are all directly appended.

Fields with unique names

Fields with physical names that are unique across all the source tables are added to the output table but not directly appended to any other field.

In the diagram below, fields A and E are examples of this method of appending fields.

When to append

Use appending when you want to combine data from multiple tables with an identical or similar structure. For example, appending is a good choice for combining monthly or quarterly tables into an annual table.

When appending is less suitable

Appending is generally not a substitute for joining or relating because it does not allow you to include or exclude records based on matched or unmatched values in a common key field. With appending, all records from each source table are included in the output table.

Appending completely dissimilar tables

You can append completely dissimilar tables – that is, two or more tables that do not have any fields in common. While not the primary intended use of appending, there may be instances in which appending dissimilar tables serves an analytical purpose.

Including all fields or only common fields

When you append tables you have two options:

• include all fields from all source tables
• include only those fields that are common to all source tables, meaning that they occur in every table

For fields to be considered common they must have an identical physical name, and belong to the same data category:

• Character
• Numeric
• Datetime
• Logical

Automatic harmonization

In some situations Analytics automatically harmonizes fields in order to append them:

Data category of fields	Harmonization performed
Character	Different field lengths are harmonized. Different character data types such as Custom, PCASCII, and EBCDIC are harmonized by converting the fields to the ASCII or UNICODE data type.
Numeric	Different field lengths are harmonized. The fields are converted to the ACL data type. A different number of defined decimal places are harmonized. Decimal places are standardized on the greatest number of places, with trailing zeros added to numeric values where necessary. The fields are converted to the ACL data type. Different numeric data types such as Print, Float, EBCDIC, and Micro are harmonized by converting the fields to the ACL data type.
Datetime	Different date, datetime, or time formats in the source data are harmonized by converting the fields to the Analytics default formats: YYYYMMDD YYYYMMDD hh:mm:ss hh:mm:ss

When automatic harmonization is not performed

Analytics does not automatically harmonize fields in the following situations. An error message appears and the append operation is not executed.

• Two fields with an identical name belong to different data categories.
• Two datetime fields with an identical name belong to different datetime subtypes (date, datetime, or time).
• Two datetime fields with an identical name are inconsistent in their use of the time zone indicator.

If you encounter one of these situations, see User-specified harmonization.

User-specified harmonization

Two options in the Append dialog box allow you to harmonize identically named fields belonging to different data categories so that the fields can be appended without error. The options work by standardizing identically named fields on the character data category:

• Use Character data type to harmonize common fields with different data types converts non-character fields to the character data category only when required for harmonization
• Convert all fields to Character data type converts all non-character fields in all tables being appended to the character data category whether required for harmonization or not

Computed fields are not supported

You cannot append computed fields. When you append tables, any computed fields in the source tables are automatically excluded from the output table.

If a computed field in a source table has the same name as a physical field in another source table, an error message appears and the append operation is not executed.

Record Note fields are not supported

You cannot append Record Note fields. When you append tables, any Record Note fields in the source tables are automatically excluded from the output table.

If a Record Note field in a source table has the same name as a physical field in another source table, an error message appears and the append operation is not executed.

A Record Note field is automatically generated by Analytics when you add a note to a record. For more information, see Add or edit record notes.

Additional information about appending

The table below provides additional information about appending.

Functional area	Details
Record length	If you include all fields from all source tables when appending, the record length in the output table can be longer than the longest record in the source tables. An error message appears if the output record length exceeds the Analytics maximum of 32 KB.
Datetime fields	For two or more datetime fields to be appended, the following conditions must be met: identical physical names identical data category (Datetime) identical data subtypes (date, datetime, or time) identical use of time zone indicator – either used, or not used, by all fields being appended Note You can harmonize dissimilar datetime fields by converting them to the character data category, and then append them. This approach allows you to combine the data in a single table. However, depending on the nature of the source data, you may not be able to convert the combined data back to datetime data.
Decimal places	Specific behavior governs the appending of numeric fields that include decimal places. The Decimal setting The append operation uses the number of decimal places defined in the Dec setting in the field definition in the table layout. Note The Dec setting may not be the same as the actual number of decimal places in the source data. Decimal places that exceed the Dec setting are undefined, and are rounded in calculations. Inconsistent Decimal settings If appended numeric fields have inconsistent Dec settings, the fields are converted to the ACL data type and automatically harmonized on the longest Dec setting. Any decimal places in source data files that exceed the longest Dec setting are excluded from the output table generated by the append operation. Consistent Decimal setting If appended numeric fields have a consistent Dec setting, no data type conversion or harmonization occurs. Any decimal places in source data files that exceed the Dec setting are included in the output table generated by the append operation.
Sorting	Any existing sort orders in the source tables are separately maintained in the respective record sets in the output table. Even if the records in all source tables are sorted, the output table is considered unsorted because the source records are appended as groups, without consideration of any existing sort order in other source tables. For example, if you append monthly or quarterly tables to create an annual table, any internal sorting of the monthly or quarterly data is retained. If required, you can sort the output table after performing the append operation.
Field order	Common fields Common fields in source tables do not have to be in the same order to be appended. For example, these fields are correctly appended even though they are in a different order: Table Fields Table 1 Last_name | First_name | Middle_name Table 2 First_name | Middle_name | Last_name The first table selected in the Append dialog box dictates the order of the fields in the output table. So in the example above, the order in the output table is: Last_name | First_name | Middle_name Non-common fields Non-common fields in source tables appear in the output table in the order that they appear in the selected group of source tables. For example, when appending these two tables: Table Fields Table 1 Title | Last_name | First_name | Middle_name Table 2 First_name | Middle_name | Last_name | Date_of_birth the order in the output table is: Title | Last_name | First_name | Middle_name | Date_of_birth
Alternate Column Title	Alternate column titles in source tables appear in the output table. If more than one source table has an alternate column title for the same field, the title from the first selected table takes precedence.