APPEND command

Concept Information

Append tables

Combines records from two or more Analytics tables by appending them in a new Analytics table.

Syntax

APPEND table_1, table_2, <...n> TO table_name <COMMONFIELDS> <OPEN> <ASCHAR> <ALLCHAR> <SOURCETABLE>

Parameters

Name Description
table_1, table_2, <...n>

The tables to append.

The records from each table are appended in the order in which you specify the tables. The output table contains the records from table_1, followed by the records from table_2, and so on.

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

The location to send the results of the command to:

  • table_name saves the results to an Analytics table

    Specify table_name as a quoted string with a .FIL file extension. For example: TO "Output.FIL"

    By default, the table data file (.FIL) is saved to the folder containing the Analytics project.

    Use either an absolute or relative file path to save the data file to a different, existing folder:

    • TO "C:\Output.FIL"
    • TO "Results\Output.FIL"

    Note

    Table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number.

COMMONFIELDS

optional

Only those fields that are common to all tables being appended are included in the output table.

If you omit COMMONFIELDS, all fields from all tables are included in the output table. Blank values appear in the output table where no fields exist in the source tables.

Tip

For diagrams and screen captures illustrating the two options, see Appending tables.

Note

The APPEND command does not support appending computed fields. For more information, see Computed fields not supported.

What makes fields common?

For fields to be considered common they must:

  • occur in every source table
  • have an identical physical name
  • belong to the same data category:
    • Character
    • Numeric
    • Datetime
    • Logical

Identical name, different data category

If two fields have an identical name but belong to different data categories, an error message appears and the APPEND command is not executed.

The error message contains all data category conflicts in the set of tables specified by APPEND. The message is saved to the command log.

Note

You can avoid this situation by using either ASCHAR or ALLCHAR to harmonize data categories.

OPEN

optional

Opens the table created by the command after the command executes. Only valid if the command creates an output table.

ASCHAR

optional

Harmonizes fields with identical names but different data categories by converting non-character fields to the character data category.

For example, you append two tables in which the Employee_ID field is character data in one table, and numeric data in the other. The numeric Employee_ID field is converted to character data and the two fields are appended without an error.

ASCHAR is ignored if ALLCHAR is also specified.

ALLCHAR

optional

Converts all non-character fields in all tables being appended to the character data category.

This global conversion to character data ensures that all identically named fields are appended without error.

Note

After appending, you can change the data category of an entire appended field if appropriate for the data contained by the field.

SOURCETABLE

optional

Include the Source Table field ( Source_Table ) in the output table.

For each record in the output table, the Source Table field identifies the table from which the record originated.

Tip

Including the names of the source tables you are appending may provide useful information when you analyze data in the output table.

Examples

Append three monthly transaction tables

The example below appends three monthly transaction tables and outputs a quarterly transaction table that includes all fields from the three source tables:

APPEND Trans_Jan, Trans_Feb, Trans_Mar TO Trans_Q1

Append three employee tables and include only common fields

The example below appends three divisional employee tables and outputs a master employee table that includes only common fields from the three source tables:

APPEND Employees_central, Employees_east, Employees_west TO Employees_master COMMONFIELDS

Append three employee tables and harmonize fields with different data categories

The examples below append three divisional employee tables in which some identically named fields use different data categories.

The first example converts non-character fields to the character data category only when required for harmonization:

APPEND Employees_central, Employees_east, Employees_west TO Employees_master ASCHAR

The second example converts all non-character fields to the character data category whether required for harmonization or not:

APPEND Employees_central, Employees_east, Employees_west TO Employees_master ALLCHAR

Remarks

For more information about how this command works, see Appending tables.

How it works

The APPEND command combines records from two or more tables by appending them and creating a new table. Appending means to add one group of records to the bottom of another group of records.

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

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.

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 use ASCHAR or ALLCHAR to harmonize the data category of the fields.)

When to use APPEND

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

Tip

A single execution of the APPEND command can replace multiple executions of the EXTRACT command with the APPEND option.

Not a substitute for JOIN or DEFINE RELATION

APPEND is generally not a substitute for the JOIN or DEFINE RELATION commands because it does not allow you to include or exclude records based on matched or unmatched values in a common key field. With APPEND, 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 the APPEND command, there may be instances in which appending dissimilar tables serves an analytical purpose.

Appending 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

If two datetime fields have an identical name but fail to meet one of the other conditions an error message appears and the APPEND command is not executed.

The error message contains all failed conditions in the set of tables specified by APPEND. The message is saved to the command log.

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.

Automatic harmonization

In some situations the APPEND command 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.

Note

User-specified harmonization of fields with identical names but different data categories is explained above. For more information, see ASCHAR and ALLCHAR.

Computed fields not supported

The APPEND command does not support appending 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 command 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 command.) 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 not supported

The APPEND command does not support appending 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 command is not executed.

A Record Note field is automatically generated by Analytics when you add a note to a record.

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.

Appending and decimal places

Specific behavior governs the appending of numeric fields that include decimal places.

The Decimal setting

The APPEND command 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 APPEND.

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

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.

How field order works

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 specified in the APPEND command 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.