Menu: Found in the DATA menu
Use the Summarize command to generate a record count and numeric field value totals for each distinct value of key character, numeric or date fields in a sorted file.
Summarize allows you to accumulate totals of numeric fields for each distinct value of the key character fields. The number of records belonging to each distinct value is displayed in an Analyzer generated field named COUNT. The number of distinct character field values that can be summarized is almost limitless.
Because it can report on a limitless number of unique key field values, Summarize is most effective for large tables. Summarize requires a sorted file on the key character fields, so you must either sort the file first or check the Presort checkbox. For small or medium-sized unsorted files, Classify is an effective command for summarizing data. For more information, see Classify.
Summarize lets you select “Other Fields”; additional character or numeric fields to be included in your output. The first “Other” field value encountered in the sorted table belonging to a given summarized group is outputted to the summarized file as the “Other” field value for that group. You may want to include an “Other” field in the summarized file if that field’s value is constant over the entire summarized group or where there is a one-to-one relationship between the key field and another field (for example: customer number and customer name).
Tip: “Other Fields” allows selection of the values from the first record in each group of key values. So based on how the data is sorted, using “Other Fields” enables selection of (for example), the most recent record, the oldest record, the largest valued record or the smallest valued record for each group of key values.
Tip: “Other fields “ can also be used to remove duplicate records from a file. To do this, specify the character field on which to summarize, do not accumulate any fields, select “Other Fields” and then select all fields and send the output to a file. The output file will contain unique records only.
When presorting, you can additionally order the records to be summarized by selecting “Additional fields” to be presorted in addition to the key field(s) being summarized on. This is most useful when selecting “Other fields” to ensure that the first record in each set of key records being summarized contains the correct values for the chosen “Other fields”.
This command requires that all files be presorted on the key character fields in the intended summarizing sequence. If not already sorted, you can either sort or index the file or check the Presort checkbox below the key field list box.
Note: Ensure that the Presort checkbox is disabled when using an index file.
In addition to the command parameters described below, the Summarize command has the following command parameters: Append, First, If, Open, Next, Noformat, To and While. For a description of these parameters, see Command Parameters. For a description of supported field modifiers see Field Modifiers.
Indicates the character fields or expressions to be summarized. In the command dialog, select the fields from the “Summarize key fields” list box or click [Choose] to display the Selected Fields dialog. For more information on the Selected Fields dialog, see Selection/Edit Dialog Boxes.
Specifies the numeric fields or expressions to be totalled by Analyzer for each distinct key character field value. In the command dialog, select the fields from the “Fields to accumulate” list box or click [Choose] to display the Selected Fields dialog.
Indicates the additional fields or expressions to be included in the summarized records. Fields already selected from the “Summarize Key Fields” and “Fields to accumulate” list boxes need not be selected here again. The resulting value in the summarized file is selected from the first record belonging to each summarized group encountered in the sorted table. In the command dialog, click the [More] button, then select the fields from the “Other fields” list box or click [Choose] to display the Selected Fields dialog.
If Presort is specified, allows you to specify the field(s) in addition to those being summarized on, to presort the data upon. In the command dialog, click the [More] button, then in the “Sort Options” click the “Additonal Field(s)” radio button and then either select the fields in the list box (in sort order) or click [Choose] to display the Selected Fields dialog.
Tip: It may be useful to also include the additional presort fields as Other Fields so that you can see their value in the resulting summarized file.
By default the Summarize command outputs to a data file. Select the Output to Screen checkbox to generate screen output with drill-down hyperlinks on record counts or accumulated numeric totals. For command syntax, simply omit the To Filename option and the output automatically goes to the screen.
SUMMARIZE ON key-fields <ACCUMULATE summarize-fields>
<OTHER other-fields>
<TO data-file-name>
<IF test> <WHILE test> <FIRST|NEXT range>
<PRESORT {field-list}> <NOFORMAT> <APPEND> <OPEN>¿
Note: You cannot use the presort keyword in a command group within a procedure.
To summarize an inventory table by location and within each location by product class, do the following:
| 1. | Open the inventory table. |
| 2. | Click the Data menu and select Summarize. |
| 3. | In the “Summarize Key Fields” list box select the location field first, then the product class field. |
| 4. | In the “Fields to Accumulate” list box select the quantity on hand and market value fields. |
| 5. | If your table is not sorted, check the Presort checkbox. Finally, specify an output file name and click [OK]. |
The resulting summarized table appears.