CLASSIFY command

Concept Information

Classifying data

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 <EXCLUDE numeric_field <...n>>> <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.
EXCLUDE numeric_field

optional

Only valid when using SUBTOTAL ALL.

The field or fields to exclude from the command. EXCLUDE allows you to fine-tune SUBTOTAL ALL, by excluding the specified fields.

EXCLUDE must immediately follow SUBTOTAL ALL. For example:

SUBTOTAL ALL EXCLUDE field_1 field_2
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

For more information about how this command works, see Classifying data.

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