STRATIFY command
Concept Information
Groups 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>|SUBTOTAL ALL <EXCLUDE numeric_field <...n>>> <KEY break_field> <TO {SCREEN|table_name|filename|GRAPH|PRINT}> <LOCAL> <IF test> <FIRST range|NEXT range> <WHILE test> <APPEND> <OPEN> <HEADER header_text> <FOOTER footer_text> <STATISTICS>
Parameters
Name | Description |
---|---|
ON numeric_field |
The numeric field or expression to be stratified. |
MINIMUM value |
Applies to numeric fields only. The minimum value of the first numeric interval. MINIMUM is optional if you are using FREE, otherwise it is required. |
MAXIMUM value |
Applies to numeric fields only. The maximum value of the last numeric interval. MAXIMUM is optional if you are using FREE, otherwise it is required. |
INTERVALS number optional |
Applies to numeric fields only. The number of equal-sized intervals Analytics 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 |
Applies to numeric fields only. Creates 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: FREE -1000, 0, 1000, 2000, 3000 If you specify both FREE and INTERVALS, then INTERVALS is ignored. |
SUPPRESS optional |
Values above the MAXIMUM value and below the MINIMUM value are excluded from the command output. |
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. If you do not select a subtotal field, the field you are stratifying on is automatically subtotaled. You must explicitly specify 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. |
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 |
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. |
TO SCREEN table_name | filename | GRAPH | PRINT |
The location to send the results of the command to:
|
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). |
FIRST range | NEXT range optional |
The number of records to process:
Use range to specify the number of records to process. If you omit FIRST and NEXT, all records are processed by default. |
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. |
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:
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. |
OPEN optional |
Opens the table created by the command after the command executes. Only valid if the command creates an output table. |
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. |
STATISTICS optional |
Note Cannot be used unless SUBTOTAL is also specified. Calculates average, minimum, and maximum values for all SUBTOTAL fields. |
Examples
Stratifying on invoice amount
You need to stratify an accounts receivable table on the Invoice_Amount field. The invoice amount is also automatically subtotaled.
The output is grouped into $1000 intervals:
- from $0 to $999.99
- from $1,000 to $1,999.99
- so on
The total invoice amount is included for each interval.
OPEN Ar
STRATIFY ON Invoice_Amount MINIMUM 0 MAXIMUM 10000 INTERVALS 10 TO "Stratified_invoices.FIL"
Remarks
For more information about how this command works, see Stratifying data.
How it works
STRATIFY groups records into equal-sized, or custom-sized, numeric intervals based on values in a numeric field.
The output contains a single record for each interval, with a count of the number of records in the source table that fall into each interval.
Automatically populate the MINIMUM and MAXIMUM values
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.
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 |