CLASSIFY command

Groups records based on identical values in a character or numeric field. Counts the number of records in each group, and also subtotals specified numeric fields for each group.

Syntax

CLASSIFY <ON> key_field <SUBTOTAL numeric_field <...n>|SUBTOTAL ALL> <INTERVALS number> <SUPPRESS> <TO {SCREEN|table_name|GRAPH|PRINT}>  <LOCAL> <IF test> <WHILE test> <FIRST range|NEXT range> <HEADER header_text> <FOOTER footer_text> <KEY break_field> <OPEN> <APPEND> <STATISTICS>

Parameters

Name	Description
ON key_field	The character or numeric field to classify. Maximum key field length is 2048 characters. If you want to classify a table using a key field longer than 2048 characters, use the SUMMARIZE command. It does not restrict key field length.
SUBTOTAL numeric_field <...n> \| SUBTOTAL ALL optional	One or more numeric fields or expressions to subtotal for each group. Multiple fields must be separated by spaces. Specify ALL to subtotal all the numeric fields in the table.
INTERVALS number optional	The maximum number of groups in the output result. If the number of sets of identical values in the field being classified exceeds the specified maximum, sets are used starting from the top of the column. Sets exceeding the maximum are grouped together in a group called "OTHER". If INTERVALS is omitted, a group is created for each set of identical values in the field being classified. Note This parameter is not available in the Analytics user interface and can only be used as part of ACLScript syntax in a script or the command line.
SUPPRESS optional	Note Cannot be used unless INTERVALS is also specified. SUPPRESS is not available in the Analytics user interface and can only be used as part of ACLScript syntax in a script or the command line. Excludes sets of identical values exceeding the maximum specified by INTERVALS from the command output.
TO SCREEN \| table_name \| GRAPH \| PRINT	The location to send the results of the command to: SCREEN displays the results in the Analytics display area Tip You can click any linked result value in the display area to drill down to the associated record or records in the source table. table_name saves the results to an Analytics table Specify table_name as a quoted string with a .FIL file extension. For example: TO "Output.FIL" By default, the table data file (.FIL) is saved to the folder containing the Analytics project. Use either an absolute or relative file path to save the data file to a different, existing folder: TO "C:\Output.FIL" TO "Results\Output.FIL" Note Table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number. GRAPH displays the results in a graph in the Analytics display area PRINT sends the results to the default printer
LOCAL optional	Saves the output file in the same location as the Analytics project. Note Applicable only when running the command against a server table with an output file that is an Analytics table. The LOCAL parameter must immediately follow the TO parameter.
IF test optional	A conditional expression that must be true in order to process each record. The command is executed on only those records that satisfy the condition. Note The IF parameter is evaluated against only the records remaining in a table after any scope parameters have been applied (WHILE, FIRST, NEXT).
WHILE test optional	A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached. Note If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached.
FIRST range \| NEXT range optional	The number of records to process: FIRST start processing from the first record until the specified number of records is reached NEXT start processing from the currently selected record until the specified number of records is reached Use range to specify the number of records to process. If you omit FIRST and NEXT, all records are processed by default.
HEADER header_text optional	The text to insert at the top of each page of a report. header_text must be specified as a quoted string. The value overrides the Analytics HEADER system variable.
FOOTER footer_text optional	The text to insert at the bottom of each page of a report. footer_text must be specified as a quoted string. The value overrides the Analytics FOOTER system variable.
KEY break_field optional	The field or expression that groups subtotal calculations. A subtotal is calculated each time the value of break_field changes. break_field must be a character field or expression. You can specify only one field, but you can use an expression that contains more than one field.
OPEN optional	Opens the table created by the command after the command executes. Only valid if the command creates an output table.
APPEND optional	Appends the command output to the end of an existing file instead of overwriting it. Note You must ensure that the structure of the command output and the existing file are identical: the same fields the same field order matching fields are the same length matching fields are the same data type Analytics appends output to an existing file regardless of its structure. If the structure of the output and the existing file do not match, jumbled, missing, or inaccurate data can result.
STATISTICS optional	Note Cannot be used unless SUBTOTAL is also specified. Calculates average, minimum, and maximum values for all SUBTOTAL fields.

Examples

Total transaction amount per customer

You want to classify an accounts receivable table on the Customer_Number field and subtotal the Trans_Amount field. The output results are grouped by customer, and include the total transaction amount for each customer:

OPEN Ar 
CLASSIFY ON Customer_Number SUBTOTAL Trans_Amount TO "Customer_total.FIL"

Total, average, minimum, and maximum transaction amounts per customer

As with the previous example, you classify an accounts receivable table on the Customer_Number field and subtotal the Trans_Amount field.

Now you include STATISTICS to calculate the average, minimum, and maximum transaction amounts for each customer:

OPEN Ar 
CLASSIFY ON Customer_Number SUBTOTAL Trans_Amount TO "Customer_stats.FIL" STATISTICS

Identical invoice amounts

You need to identify invoice amounts that appear more than once in the Ap_Trans table.

To do this, you classify the table on the Invoice_Amount field. The output results are grouped by invoice amount with an associated count that you can use to identify any invoice amounts that occur more than once:

OPEN Ap_Trans
CLASSIFY ON Invoice_Amount TO "Grouped_invoice_amounts.FIL" OPEN
SET FILTER TO COUNT > 1

Remarks

Note

For more information about how this command works, see the Analytics Help.

How it works

CLASSIFY groups records that have the same value in a character or numeric field.

Output contains a single record for each group, with a count of the number of records in the source table that belong to the group.

Sorting and CLASSIFY

CLASSIFY can process either sorted or unsorted data. Output is automatically sorted in ascending order.

Names of auto-generated subtotal and statistics fields

If you use STATISTICS to perform statistical calculations on one or more SUBTOTAL fields, and output the results to an Analytics table, the fields auto-generated by the parameters have the following names:

Description of auto-generated field	Field name in output table	Alternate column title (display name) in output table
Subtotal field	subtotaled field name in source table	Total + subtotaled alternate column title in source table
Average field	a_subtotaled field name in source table	Average + subtotaled alternate column title in source table
Minimum field	m_subtotaled field name in source table	Minimum + subtotaled alternate column title in source table
Maximum field	x_subtotaled field name in source table	Maximum + subtotaled alternate column title in source table