STRATIFY command

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

Syntax

STRATIFY <ON> numeric_field MINIMUM value MAXIMUM value
{<INTERVALS number>|FREE interval_value <...n> last_interval} <SUPPRESS>
<SUBTOTAL numeric_field <...n>> <KEY break_field>
<TO SCREEN|table_name|filename|GRAPH|PRINT> <IF test> <{FIRST|NEXT} range>
<WHILE test> <APPEND> <OPEN> <HEADER header_text> <FOOTER footer_text>
<LOCAL> <STATISTICS>

Parameters

ON numeric_field

Specifies the numeric field or expression to be stratified. The ON parameter can be omitted.

MINIMUM value

Specifies the minimum value of the first numeric interval. MINIMUM is optional if you are using the FREE parameter, otherwise it is required.

MAXIMUM value

Specifies the maximum value of the last numeric interval. MAXIMUM is optional if you are using the FREE parameter, otherwise it is required.

INTERVALS number

Optional. Specifies the number of equal-sized intervals ACL produces over the range specified by the MINIMUM and MAXIMUM values. If you do not specify a number of intervals, the default number is used. The default is specified by the Intervals number on the Command tab in the Options dialog box.

FREE interval_value <...n> last_interval

Optional. Allows you to create custom-sized intervals by specifying the start point of each interval and the end point of the last interval.

If you specify MINIMUM and MAXIMUM values, those values are the start point of the first interval and the end point of the last interval, and each interval_value creates an additional interval within the range. The interval values you specify must be greater than the MINIMUM value, and equal to or less than the MAXIMUM value.

Interval values must be in numeric sequence and cannot contain duplicate values. For example, FREE -1000, 0, 1000, 2000, 3000

If you specify both the FREE parameter and the INTERVALS parameter, the INTERVALS parameter is ignored.

SUPPRESS

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

SUBTOTAL numeric_field <...n>

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

If you do not select a subtotal field, the field you are stratifying on is automatically subtotaled. You must explicitly select the stratify field if you want to subtotal it along with one or more other fields, or if you want to include statistics for the subtotaled stratify field.

KEY break_field

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

TO SCREEN | table_name | filename | GRAPH | 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 save 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 save the .FIL file to an existing folder.

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

  • Specify TO filename to save the results to a file. You must specify the filename value as a quoted string with the appropriate file extension. For example, specify the .TXT file extension to create a text file.

    For example: TO "Output.TXT"

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

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

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

  • Specify TO GRAPH to display the results in a graph in the ACL display area.

IF test

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

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.

WHILE test

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

APPEND

Optional. Specifies that command output is appended to the end of an existing file instead of overwriting it. When using this parameter, you must ensure that the structure of the command output and the existing file is identical, if maintaining a consistent file structure is important (for example, 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 is opened after the command executes. This parameter is only valid if the command creates an output 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.

LOCAL

Optional. Specifies that the output file is 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.

STATISTICS

Optional. Specifies that average, minimum, and maximum values are calculated for all SUBTOTAL fields. If you specify the STATISTICS parameter you must also specify the SUBTOTAL parameter with at least one field.

Remarks

The STRATIFY command groups records into equal-sized, or custom-sized, numeric intervals based on values in a numeric field. The output results contain a single record for each interval, with a count of the number of records in the source table that fall into each interval.

You can run the STATISTICS or PROFILE commands on the stratify field before running the STRATIFY command to automatically populate the MINIMUM and MAXIMUM parameter values with the lowest and highest values in the field.

If you use the optional STATISTICS parameter, average, minimum, and maximum values are calculated for each subtotaled numeric field. If you output the results to an ACL table, the fields generated by the SUBTOTAL and STATISTICS parameters use the following naming conventions:

Table 1. Naming conventions for subtotal and statistics fields

Subtotal field

Average field

Minimum field

Maximum field

field name in output table

subtotaled field name in source table

a_subtotaled field name in source table

m_subtotaled field name in source table

x_subtotaled field name in source table

alternate column title in output table

(the column display name in the view)

Total + subtotaled alternate column title in source table

Average + subtotaled alternate column title in source table

Minimum + subtotaled alternate column title in source table

Maximum + subtotaled alternate column title in source table

Example

The following example stratifies an accounts receivable table on the Invoice Amount field, which is also automatically subtotaled. The output results are grouped into $1000 intervals – invoices from $0 to $999.99, from $1,000 to $1,999.99, and so on – and include the total invoice amount for each interval.

OPEN Ar
STRATIFY ON Invoice_Amount MINIMUM 0 MAXIMUM 10000 INTERVALS 10 TO
"Stratified_invoices.FIL"


(C) 2015 ACL Services Ltd. All Rights Reserved.