CROSSTAB command
Concept Information
Groups records based on identical combinations of values in two or more character or numeric fields, and displays the resulting groups in a grid of rows and columns. Counts the number of records in each group, and also subtotals specified numeric fields for each group.
Syntax
CROSSTAB {ON row_field <...n>|ON ALL <EXCLUDE field_name <...n>>} COLUMNS column_field <SUBTOTAL numeric_field <...n>|SUBTOTAL ALL <EXCLUDE numeric_field <...n>>> TO {SCREEN|table_name|filename|GRAPH|PRINT} <LOCAL> <IF test> <WHILE test> <FIRST range|NEXT range> <APPEND> <COUNT> <OPEN> <HEADER header_text> <FOOTER footer_text>
Parameters
Name | Description |
---|---|
ON row_field <...n> | ON ALL |
One or more character or numeric fields or expressions to use for rows in the resulting grid of rows and columns.
|
EXCLUDE field_name optional |
Only valid when using ON ALL. The field or fields to exclude from the command. EXCLUDE allows you to fine-tune ON ALL, by excluding the specified fields. EXCLUDE must immediately follow ON ALL. For example: ON ALL EXCLUDE field_1 field_2 |
COLUMNS column_field |
The character or numeric field or expression to use for columns in the resulting grid of rows and columns. You can specify only one field or expression for the columns. |
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 |
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). |
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:
Use range to specify the number of records to process. If you omit FIRST and NEXT, all records are processed by default. |
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. |
COUNT optional |
Includes record counts as columns. Counts are useful when you use SUBTOTAL. Counts are automatically included if you do not select any subtotal fields. |
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. |
Examples
Cross-tabulating an accounts receivable table with SUBTOTAL
You want to cross-tabulate an accounts receivable table on the Customer Number and Transaction Type fields. You also want to subtotal the Transaction Amount field.
The output is grouped by customer, and within each customer by transaction type. It includes the total transaction amount for each customer for each transaction type:
OPEN Ar
CROSSTAB ON Customer_Number COLUMNS Trans_Type SUBTOTAL Trans_Amount COUNT TO SCREEN
Cross-tabulating an accounts receivable table to find duplicate transactions
You need to find evidence of duplicate transactions in an accounts receivable table.
To do this, you cross-tabulate an accounts receivable table on the Transaction Amount and Transaction Type fields. The output groups and counts identical transaction amounts for each transaction type:
OPEN Ar
CROSSTAB ON Trans_Amount COLUMNS Trans_Type TO SCREEN
Remarks
For more information about how this command works, see Cross-tabulating data.
How it works
CROSSTAB groups records that have the same combination of values in two or more character or numeric fields.
The output contains a grid of rows and columns similar to a pivot table. It includes a single row-column intersection for each group, with a count of the number of records in the source table that belong to the group.
Sorting and CROSSTAB
CROSSTAB can process either sorted or unsorted data. Both the row_field and the column_field in the output are automatically sorted in ascending order.
If you specify more than one row_field, the fields use a nested sort. The order of the nesting follows the field order you specify, or the order of the fields in the table layout if you use ON ALL.