GROUP command
Executes one or more ACLScript commands on a record before moving to the next record in the table, with only one pass through the table. Command execution can be controlled by conditions.
Syntax
GROUP <IF test> <WHILE test> <FIRST range|NEXT range> command <...n> <ELSE IF test> command <...n> <ELSE> command <...n> END
Note
Some Analytics commands cannot be used with the GROUP command. For more information, see Commands that can be used inside the GROUP command.
Parameters
| Name | Description |
|---|---|
| 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. |
| command <...n> |
One or more ACLScript commands to execute inside the GROUP. For a complete list of commands supported inside GROUP, see Commands that can be used inside the GROUP command. If there is a preceding IF or ELSE IF, the test must evaluate to true. If the command is listed under ELSE, the command is executed if there are records that have not been processed by any of the preceding commands. You can include multiple commands, with each command starting on a separate line. |
| ELSE IF test optional |
Opens an ELSE IF block for the GROUP command. The condition tests records that did not match the GROUP command test, or any previous ELSE IF tests. You can include multiple ELSE IF tests and they are evaluated from top to bottom, until the record evaluates to true and the commands that follow that ELSE IF statement are executed. |
| ELSE optional |
Opens an ELSE block for the GROUP command. The commands that follow are executed for records that evaluated to false for all of the previous tests. |
| END | The end of the GROUP command. |
Examples
Simple GROUP
Simple groups start with a GROUP command, are followed by a series of commands, and terminate with an END command:
GROUP COUNT HISTOGRAM ON Quantity MINIMUM 0 MAXIMUM 100 INTERVALS 10 CLASSIFY ON Location SUBTOTAL Quantity END
GROUP IF
Conditional groups execute commands based on whether a condition is true or false. The following GROUP command is executed only on records with a Product_class value less than 5:
GROUP IF Product_class < "05" COUNT HISTOGRAM ON Quantity MINIMUM 0 MAXIMUM 100 INTERVALS 10 CLASSIFY ON Location SUBTOTAL Quantity END
GROUP IF ...ELSE
Records that do not meet the test condition are ignored unless you include an ELSE block.
Any number of commands can follow an ELSE statement. In the following example, all records that do not meet the condition are processed by having their Quantity field totaled:
GROUP IF Product_class < "05" COUNT HISTOGRAM ON Quantity MINIMUM 0 MAXIMUM 100 INTERVALS 10 CLASSIFY ON Location SUBTOTAL Quantity ELSE TOTAL Quantity END
GROUP IF...ELSE IF...ELSE
You can include multiple ELSE IF blocks within a group, as long as each ELSE IF block contains a different test. In the following example, the ELSE IF blocks, and the ELSE block, produce four totals:
GROUP IF Product_class < "05" COUNT HISTOGRAM ON Quantity MINIMUM 0 MAXIMUM 100 INTERVALS 10 CLASSIFY ON Location SUBTOTAL Quantity ELSE IF Product_class = "05" TOTAL Quantity ELSE IF Product_class = "06" TOTAL Quantity ELSE IF Product_class = "07" TOTAL Quantity ELSE TOTAL Quantity END
Nested GROUP commands
Nested groups refer to groups contained within other groups. Nested groups provide a powerful way for you to control which commands are executed for which records. Most applications do not require such an advanced level of functionality, but it is available, if necessary.
As with other groups, use the END command to terminate a nested group. Analytics starts processing the data only after all group commands have been terminated:
GROUP IF Product_class < "05"
COUNT
STRATIFY ON Quantity SUBTOTAL Quantity MIN 0 MAX 100 INT 10
GROUP IF Quantity > 0
STATISTICS ON Quantity
HISTOGRAM ON Quantity
END
ELSE
TOTAL Quantity
END
In this example, all of the commands from COUNT up to and including the next GROUP are executed only if Product_class is less than 05.
The STATISTICS and HISTOGRAM commands are executed if Quantity is greater than zero. However, because the second GROUP command is nested, the STATISTICS and HISTOGRAM commands are executed only for records that meet the conditions Product_class < "05" and Quantity > 0.
Generating system variables inside a GROUP
You can use GROUP to create multiple system variables for a single command.
Normally, when you run a command such as TOTAL, COUNT, or STATISTICS, only one system variable is generated. Each time you run the command, you overwrite the value from the last execution of the command. Commands that run inside a GROUP create a specific variable for each instance of the command inside the GROUP.
In this example, the TOTAL command calculates the sum of the Amount field for each product class in the Metaphor_Trans_2002 table. When the code runs, the following variables are generated and can be used in subsequent commands after the GROUP:
- TOTAL2 the sum of the Amount field for product class 03
- TOTAL3 the sum of the Amount field for product class 05
- TOTAL4 the sum of the Amount field for product class 08
- TOTAL5 the sum of the Amount field for product class 09
OPEN Metaphor_Trans_2002 GROUP TOTAL AMOUNT IF PRODCLS = "03" TOTAL AMOUNT IF PRODCLS = "05" TOTAL AMOUNT IF PRODCLS = "08" TOTAL AMOUNT IF PRODCLS = "09" END CLOSE Metaphor_Trans_2002
Remarks
Tip
For a detailed tutorial covering the GROUP and LOOP commands, see Grouping and looping.
Commands that can be used inside the GROUP command
The table below lists the Analytics commands that can be used inside the GROUP command.
If a command is not listed below, it cannot be used inside GROUP.
| AGE | ASSIGN | BENFORD |
| CLASSIFY | COMMENT | COUNT |
| CROSSTAB | DUPLICATES | EXPORT |
| EXTRACT | GAPS | GROUP |
| HISTOGRAM | JOIN | LIST |
| LOOP | MERGE | PROFILE |
| REPORT | SEQUENCE | STATISTICS |
| STRATIFY | SUMMARIZE | TOTAL |
| VERIFY |
Grouping and looping
The GROUP command allows you to execute several commands on a record before moving to the next record in the table, which can significantly reduce processing time.
You can use the LOOP command inside the GROUP command if you need to execute a series of commands more than once against a record.
Using variables with GROUP
User-defined variables
To use a variable inside the GROUP command, define the variable before you enter the GROUP block.
Note
While you can initialize and define a variable inside a GROUP block, it is not recommended. Variables initialized inside a GROUP may cause unexpected results when used.
Inside a GROUP, you can evaluate variables using variable substitution. The value of the variable remains the same as when the GROUP is entered.
You cannot define a variable inside a GROUP and then reference it using variable substitution:
ASSIGN v_test = "hello" GROUP ASSIGN v_test2 = "%v_test% world" COMMENT this would be invalid: v_test3 = "%v_test2% again" END
System-defined variables
Certain commands such as TOTAL and STATISTICS generate system variables based on calculations that the commands perform. If you use a GROUP to execute these commands, any system variables that result are numbered consecutively, starting at the line number of the command inside the GROUP (excluding empty lines) and running to n. The value of n increases by 1 for each line number in the GROUP.
Note
You must wait until the GROUP completes before using any system generated variables created inside the GROUP. The command must run against each record in the table before the variable is available. Use these variables after the closing END keyword of the GROUP.
In the following example, the first TOTAL command generates the variable TOTAL2 and the second generates TOTAL4. Both of these variables are available to use in subsequent commands once the GROUP completes:
GROUP TOTAL Discount IF Order_Priority = "Low" ASSIGN v_var = "test" TOTAL Discount IF Order_Priority = "High" END
Syntax notes
- The multiline syntax listed for the GROUP command is required, and therefore the GROUP command cannot be entered in the command line.
- Each GROUP command must be terminated with an END command.
- When you use the GROUP command in your scripts, you can improve the readability of the command block by indenting the commands listed inside the group.
