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.

Tip

If you want to directly append inconsistently named fields, standardize the physical names of the fields in the table layouts before appending. (Assumes that the fields belong to the same data category, or that you harmonize the data category of the fields.) For more information, see Define a physical field.

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.

Tip

A single execution of the append operation can replace multiple executions of the extract and append operation.

Example

Scenario

You want to perform analysis on an entire year's worth of data but the data is spread among twelve monthly transaction tables.

Approach

You append the data from the twelve monthly tables into a single annual table containing all the data, and then perform the analysis.

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

Example: Appending three employee tables

You want to append three employee tables into an employee master table before performing analysis on employee data.

The three tables have three common fields, which appear in every table:

  • Employee_number
  • First_name
  • Last_name

and two non-common fields, which appear in one or more tables, but not in every table:

  • Middle_name
  • Email

Input

The three tables being appended appear below:

Table name Fields
Employees_central
Employees_east
Employees_west

Output – all fields included

If you include all fields, the output table contains all the records and all the fields from the three appended tables.

Blank values appear in the output table where no fields exist in the source tables.

Table name Fields
Employees_master

Output – only common fields included

If you include only common fields, the output table contains all the records and only the common fields from the three appended tables.

Table name Fields
Employees_master

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

Example

Scenario

You want to append two tables in which the Employee_ID field is character data in one table, and numeric data in the other.

Approach

In the Append dialog box, you select Use Character data type to harmonize common fields with different data types. The numeric Employee_ID field is converted to character data and the two fields are appended without an error.

Tip

If harmonizing on the character data category does not meet your needs, you may be able to manually harmonize the fields using a different approach, or redefine one or more fields. For more information, see Harmonizing fields and Define a physical field.

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.

Tip

You can append a computed field by first extracting it to convert the field to a physical field. (For more information, see Extract and append data.) You then use the extracted table in the append operation.

Another approach is to recreate the computed field in the appended output table.

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.
Analytics 14.1 Help