CLASSIFY command

Used to group records into classes based on unique values in a character field. Counts the number of records in each class, and also subtotals specified numeric fields for each class.

Syntax

CLASSIFY <ON> character_field_name <TO SCREEN|table_name|GRAPH|PRINT>
<SUBTOTAL numeric_field_list> <INTERVALS n> <IF test> <WHILE test>
<FIRST|NEXT range> <HEADER header_text> <FOOTER footer_text>
<KEY break_field> <SUPPRESS> <OPEN> <APPEND> <LOCAL>

Parameters

ON character_field_name

Specifies the character field or expression to be classified. The ON parameter can be omitted.

TO SCREEN | table_name | GRAPH | PRINT
  • Specify TO SCREEN to display the results on screen in ACL. 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.

  • Specify TO GRAPH to create a graph of the command results and display it on screen in ACL.

SUBTOTAL numeric_field_list

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

INTERVALS n

Optional. Specifies the maximum number of classes in the output result. If the number of unique values in the field being classified exceeds the specified maximum, unique values are used starting from the top of the column. Unique values exceeding the maximum are grouped together in a class called OTHER.

If the INTERVALS parameter is omitted, a class is created for each unique value in the field being classified. This parameter is not available in the ACL user interface and can only be used as part of ACLScript syntax in a script or the command line.

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.

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.

KEY break_field

Optional. Specifies that a subtotal should be calculated each time the value of the specified field or expression changes. You can only specify a single field, but you can specify an expression that contains more than one field. The break_field value must be a character field or expression.

SUPPRESS

Optional. Specifies that values above the MAXIMUM value and below the MINIMUM value should be excluded from the command output.

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.

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.

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.

Remarks

The CLASSIFY command can be used with either sorted or unsorted data and is particularly useful for quick data scanning and summarizing. You can use it in a wide variety of circumstances. For example, the CLASSIFY command can rapidly generate a trial balance from unsorted ledger transactions, and can also replace the need to execute separate SORT and SUMMARIZE commands.

The limit on the number of totals depends on the available memory in your computer, but is normally 50,000 or more.

Example

The following example classifies the Inventory table on product class (ProdCls), and subtotals the inventory value at cost (Value). Inventory value at cost is the unit cost of each product multiplied by quantity on hand. The result is a table grouped by product class, with the subtotaled value of inventory for each product class, output to a new ACL table.

OPEN Inventory
CLASSIFY ON ProdCls SUBTOTAL Value TO "ProductClassValue.FIL"


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