SUMMARIZE command

Used to group records and produce record counts based on values in one or more character or datetime fields, and subtotal on specified numeric fields.

Syntax

SUMMARIZE <ON> key_field <...n> <SUBTOTAL field <...n>> <OTHER field <...n>>
<TO SCREEN|table_name|PRINT> <IF test> <WHILE test> <{FIRST|NEXT} range>
<PRESORT>  <APPEND> <OPEN> <LOCAL> <HEADER header_text> <FOOTER footer_text>
 <ISOLOCALE locale_code>

Parameters

ON key_field

Specifies one or more character or datetime fields to summarize on. Multiple fields must be separated by spaces. The ON parameter can be omitted.

SUBTOTAL field

Optional. Specifies one or more numeric fields or expressions to subtotal for each group. Multiple fields must be separated by spaces. Specify ALL to subtotal each of the numeric fields in the table.

OTHER field

Optional. Specifies one or more additional fields to include in the output.

You should only use the OTHER parameter with fields that contain the same value for all records in each summarized group. If you specify a 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.

TO SCREEN | table_name | PRINT
  • Specify TO SCREEN to display the results in the ACL display area. This is the default output if the TO parameter is not specified.

  • Specify TO table_name to write the results to an ACL table. You must specify the table_name value as a quoted string with a .FIL file extension to create an ACL table.

    For example: TO "Output.FIL"

    You can also specify an absolute or relative file path to an existing folder to write the .FIL file to.

    For example: TO "C:\Output.FIL" or TO "Results\Output.FIL"

  • Specify TO PRINT to send the results to the default printer.

IF test

Optional. Specifies a condition that must be met. The command is only executed on records that pass the test.

WHILE test

Optional. Specifies that the command should only be executed while a condition is true. The command processing terminates as soon as the specified test evaluates as false, or the end of the table is reached.

FIRST | NEXT range

Optional. Specify FIRST to start processing at the first record or NEXT to start processing at the currently selected record. Use the range value to specify the number of records to process.

PRESORT

Optional. Sorts the primary table on the primary key before executing the command. You cannot use PRESORT inside the GROUP command.

APPEND

Optional. Specifies that command output should be appended to the end of an existing file instead of overwriting the existing file. You must ensure that the structure of the two files is identical when using this parameter, if maintaining a consistent file structure is important (e.g., when you are appending data to an existing ACL table), because ACL appends output to an existing file regardless of its structure.

OPEN

Optional. Specifies that the table created by the command should be opened after the command executes. This parameter is only valid if the command creates an output table.

LOCAL

Optional. Specifies that the output file should be saved in the same location as the ACL project. This parameter only applies when the command is run on an ACL Server table and the output file is an ACL table.

HEADER header_text

Optional. Specifies the text to insert at the top of each page of a report. The header_text value must be specified as a quoted string. The value overrides any value set for the ACL HEADER system variable.

FOOTER footer_text

Optional. Specifies the text to insert at the bottom of each page of a report. The footer_text value must be specified as a quoted string. The value overrides any value set for the ACL FOOTER system variable.

ISOLOCALE locale_code

Optional. This parameter is only available in the Unicode edition of ACL. Specifies the locale. If you do not specify a locale, ACL uses the default locale. Locale codes are based on the ISO 639 standard language codes and the ISO 3166 standard country codes. Enter locale_code in the format language code_country code. For example, enter the code fr_ca for Canadian French. If you do not specify a country, ACL uses the default country for the language.

Remarks

The SUMMARIZE command counts the records for each unique value in selected character or datetime fields and optionally subtotals 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.

Because SUMMARIZE can report on any number of unique values in key character or datetime fields, the command can be very effective when used on large tables.

Example

The following example summarizes an inventory table on the Location and Product Class fields, and subtotals the Inventory Value field. The output results are grouped by location, and within each location by product class, with the subtotaled value of inventory for each unique Location-Product Class combination.

SUMMARIZE ON Location ProdCls SUBTOTAL Value TO SCREEN PRESORT


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