STRATIFY command

Concept Information

Stratifying data

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:

  • 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.

  • filename saves the results to a file

    Specify filename as a quoted string with the appropriate file extension. For example: TO "Output.TXT"

    By default, the file is saved to the folder containing the Analytics project.

    Use either an absolute or relative file path to save the file to a different, existing folder:

    • TO "C:\Output.TXT"
    • TO "Results\Output.TXT"
  • 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).
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.
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:

  • 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.
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