|
||||
 |
 |
|
||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|
|||
|
|||
|
Aggregating and Sorting on the Same Field in One Pass With BY TOTAL
When using FOCUS we can usually achieve significant efficiencies by minimizing the number of times the data is read. As an example, we often sort on summarized data in creating a report. A proven method in FOCUS was to summarize your data with an appropriate verb, such as SUM or COUNT, then HOLD the data and then read the HOLD file, sorting on the summarized column(s) to produce the final report. For years this two-pass approach was the accepted way to handle this situation. Today, however, you can combine these two operations into a single, more efficient solution by taking advantage of the BY TOTAL phrase.
Let's look at this in action with the EMPLOYEE file, which tracks employees and their current salaries and deduction amounts by deduction categories for each pay period. If we wish to compensate employees whose total deductions are 30 percent and more with a bonus, what should we do? Traditionally, we would summarize the total deduction amount for each employee; HOLD the summarized data and then read our HOLD file, sorting the summarized rows by the total deduction amount for each employee. While this approach produces the correct answer, it takes two passes of the data and would be coded something like this:
Step one – the summarization and extraction:
Step two – now we do the sorting, calculations and output:
Here's the report that is produced:
Now let's look at a more efficient solution that requires just one pass of the data and a single TABLE request. FOCUS supplies the underlying functionality for this solution by first summarizing the records to the level of the sort field(s) and then re-sorting the output when you use the TOTAL keyword in conjunction with a BY sort phrase.
This time we see just the significant records, as the IF TOTAL phrase removes non-bonus records included with the two-pass approach.
|
