Ah, the GROUP command. No other command seems to be as intimidating, and therefore underutilized, as GROUP. It is common for even experienced ACL scripters to not understand how to use this command in their scripts. Perhaps this is for good reason – at first glance the mechanics by which it executes are not obvious, which combined with the lack of decent debugging indicators makes it very difficult to work with in ACL’s scripting environment (I would love to be able to step through a GROUP block, record-by-record). It took me some time to finally crack GROUP, and when I did I found that I was mentally creating pictures of how the command executes as it processes a table. Oftentimes I close my eyes so I can better formulate and focus on the images in my mind. To help explain the GROUP command, I am going to try and recreate those mental images here. This discourse will be divided into three posts to keep post length reasonable. This first post will start with a very basic introduction and help you become familiar with the mechanics of the command. Subsequent posts will walk through more complicated examples and use cases.
Note: As we walk through the code, the results you see happen AFTER execution of the highlighted code.
Example 1 – Intro
Let’s start with the most basic GROUP command I can think of: extracting data from an existing table. This example will have very little practical value, but for those of you with no experience with GROUP, it will give you an idea of how the command works. Below is a script containing our first GROUP command and the table on which we will run it.
The GROUP command begins by moving to the first record of the table.
The EXTRACT command then operates on the current record and extracts to a new table.
At the end of the GROUP are two possible execution options: Move to the next record of the table and begin again or exit the GROUP block and proceed with the rest of the script. In this case, there are more records to process, so GROUP starts again.
The next command is the EXTRACT command. It executes on the current record, which has incremented to record 2.
This process continues until we reach the last record. Here we have just finished the EXTRACT command on the 6th record and are at END.
There are no more records to process, so the GROUP block finishes and the script executes any remaining commands.
Example 2 – Multiple Commands in the Block
Let’s do something a little more interesting with our table. For this next GROUP command, we are going to calculate the tax amount of each record and extract each category of transactions to separate tables. We will do this in 1 pass of the table rather than the customary 2 passes we would otherwise have to do. Here is the script and our table.
The start of the GROUP command takes us to the first record.
The next line calculates the tax value and saves it into a variable.
The question may arise as to why we would calculate the amount here, in a variable, rather than embed it into the two EXTRACT commands. The answer is because it makes it easier to maintain our code going forward. If we need to make changes to the tax amount calculation, we can do it once at the beginning of the GROUP command rather than multiple times in each EXTRACT. In general, if I am going to use a value more than once in my script, I encapsulate it in a variable. There is also a philosophy in the world of professional programming that takes this one step further. It states that hard-coded values, even if they are only used once, should be avoided. Rather, they should be declared into a variable with a descriptive name. That variable should then be used in the rest of the code, which makes the code more readable and maintainable. As an example, which code snippet provides a better upfront explanation of its reasoning?
EXTRACT FIELDS ALL IF AGE(StartDate, TODAY()) > 30
v_PolicyAgeLimit = 30
EXTRACT FIELDS ALL IF AGE(StartDate, TODAY()) > v_PolicyAgeLimit
In the first snippet I do not know what 30 means. Did the coder pull this number out of thin air? Is this some policy? The second snippet clarifies 30 as the age limit that is defined in a policy. Because of the clarity this approach provides, I am embracing it more and more in my ACL scripts, despite the extra lines of code that are required.
The next line extracts the current record only if the category of that record is ‘Z90’. The current record is a Z90 record, so it is extracted to Result1.
The next command in the block extracts the current record only if the category of that record is ‘A01’. The current record is NOT an A01 record, so Result2 is created as an empty table.
We have reached the end of the GROUP block and there are additional records left in the table, so the current row is incremented to row 2 and the GROUP command begins again.
The GROUP command now recalculates the tax amount for the new record.
The next command extracts the current record if the current record’s category is Z90. The current record is category A01, so no extraction occurs.
The second EXTRACT will only execute if the current record’s category is A01. This record is category A01, so the extraction occurs.
We have reached the end of the GROUP block again. The command keeps reiterating until we have processed all 6 records. Here we are at the END step of the 6th record.
At this point there are no more records to process. The GROUP command finishes and the script continues executing any subsequent commands.
This brings us to the end of the first post about GROUP. The first two examples presented here were very simple examples that should help you better understand how the command works. Subsequent posts will delve into more complicated examples that use GROUP IF, nested GROUP commands, and LOOP.