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:
|
| 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 APPENDcommand 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:
Identical name, different data categoryIf two fields have an identical name but belong to different data categories, an error message appears and the APPENDcommand 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 ALLCHARto 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. |
| 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_IDfield is character data in one table, and numeric data in the other. The numeric Employee_IDfield is converted to character data and the two fields are appended without an error. ASCHARis ignored if ALLCHARis also specified. |
| 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
Note
For more information about how this command works, see the Analytics Help.
How it works
The APPENDcommand 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 ASCHARor 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 JOINor DEFINERELATION
APPEND is generally not a substitute for the JOIN or DEFINERELATION 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 APPENDcommand automatically harmonizes fields in order to append them:
| Data category of fields | Harmonization performed |
|---|---|
| Character |
|
| Numeric |
|
| Datetime |
|
When automatic harmonization is not performed
Analyticsdoes 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 APPENDcommand 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 APPENDcommand 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 APPENDcommand 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 APPENDcommand 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 APPENDcommand 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 ACLdata 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.
