About summarizing

Summarizing allows you to count the records for each unique value in a selected character or datetime field, and subtotal numeric fields for each of these unique values – or unique combination of values if you summarize on more than one field. For example, you could summarize on customer number and transaction date fields, and subtotal the transaction amount field, to find the total daily transaction amount for each customer.

ACL reports the first value that it encounters in a summarized group and displays the number of records for that value. For example, suppose that three purchases are made on one credit card. When you summarize on the credit card number field, ACL displays one instance of the credit card number and a count of three to indicate three purchases.

Summarizing does not require that tables are sorted. However, if you summarize using a field that contains non-sequential identical values, the results contain more than one summarized group for the same value, which depending on the context can defeat the purpose of summarizing. Summarizing includes the Presort option, which allows you to sort a table as part of summarizing.

The Other Fields option

The Other Fields option allows you to select additional character, datetime, or numeric fields to include in the output. This option can provide useful information if the fields you select contain values that are the same for all records in each summarized group. For example, if you summarize on customer number and specify a customer name field as an “other field”, the customer name appears with the customer number in the output.

If you specify an “other field” that contains values that are different for a summarized group, only the first value in the group is displayed, which is not meaningful. For example, if you summarized a vendor table by state, and selected city as an “other field”, only the first city listed for each state would appear in the output. In this instance, the better approach is to summarize using both state and city as key fields, in that order.

Summarizing in detail

Summarizing counts the number of records in a table and performs the following operations:

Table 1. Operations performed by summarizing

Operation

Location in Figure 1

Groups the records based on unique values, or unique combinations of values, in one or more character or datetime fields

Cust Number field

Trans Type field

Subtotals the number of records for each unique character or datetime value, or unique combination of values

Count field

For all numeric subtotal fields included in the output results, provides subtotals for each unique character or datetime value, or unique combination of values

Trans Amount field

Optionally displays additional character, datetime, or numeric fields with complementary information

Name field

Figure 1 provides an example of the results produced by summarizing on customer number and transaction type, and subtotaling on transaction amount, in an accounts receivable table (the ‘Ar’ table in ACL DATA\Sample Data Files\Sample Project.ACL) and outputting the results to screen.

Figure 1. Summarize results

Related concepts
Classifying versus summarizing
About classifying
Related tasks
Summarizing data


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