Combining data

ACL allows you to analyze data in only one table, or one virtual table, at a time. For this reason, you may have to combine data from two or more tables into one table or virtual table as part of performing your analysis. ACL provides the following methods for combining data:

When you combine data, the method you choose is often dependent on how the data is structured. Record structure, or data structure, refers to the data elements contained in a record, the number and order of columns, and the data type and length of fields. For more information about data structure, see Data structure and data format requirements.

In some situations, it may not be immediately obvious which method to use for combining data. You may need to experiment with a small subset of the data to determine which method is best suited to the task you want to perform. Using a small subset allows you to avoid longer processing times associated with larger tables, and may also make it easier to see patterns.

Table 1 illustrates the different methods for combining data in ACL.

Table 1. Methods for combining data in ACL
Method Description

Extract/append



You can extract records or fields from one table and append them to the end of another table.

The records or fields in both tables must be exactly identical in structure. The resulting combined table is unsorted. You can repeat the extract/append operation for each additional table that needs to be combined with the target table.

Important: A best practice is to never append records to an original data file. You should create a new target table first, by extracting the records from the original table into a new table. Then extract the records from the second table, and any additional tables, and append them to the new table. This method preserves the original data file in the event you encounter any problems with the extract and append process.

Merge



You can merge records from two tables into a new, third table.

The records in the tables being merged must be exactly identical in structure. The resulting combined table is sorted. You can repeat the merge operation for each additional table that needs to be combined with the target table.

Join



Using a common key field, you can join records or fields from two tables with different record structures into a new, third table that contains any combination of fields from the two original tables.

The common key fields in the tables being joined must be exactly identical in structure. Identical key field length is not always enforced, depending on data type, but it is recommended that key fields be the same length. The resulting combined table is typically sorted, although sorting is not enforced. You can repeat the join operation for any additional tables that need to be combined with the target table.

Relate



Using a common key field from each table pair, you can relate up to 18 tables with different record structures. The resulting virtual table allows you to access and analyze data from any combination of fields in the related tables as if they existed in a single table.

The common key fields in each table pair being related must be exactly identical in structure. Identical key field length is not enforced, but it is recommended that key fields be the same length. The virtual table uses the existing sort order of the parent table in the relation.

A combination of methods

If required, you can use more than one method of combining data to achieve your goal. For example, you could compile an annual transaction table by extracting and appending monthly tables, and then use a common key field to join the annual table with a master table.

Additional data combining techniques

Variations of merging, joining, and relating provide additional flexibility when combining data:

Section contents



(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback