Grouping and looping

The GROUP and LOOP commands provide two ways to execute a series of commands repeatedly. GROUP performs a single iteration of one or more commands against each record. LOOP performs multiple iterations of a series of commands against a single record, and can only be used inside a GROUP block.

A simple example of GROUP

You have a table of invoice data called Ap_Trans. Using this data, you need to calculate a running total of invoice amounts:

Vendor_Number	Vendor_Name	Invoice_Number	Date	Amount
11663	More Power Industries	5981807	2000-11-17	618.30
13808	NOVATECH Wholesale	2275301	2000-11-17	6705.12
12433	Koro International	6585673	2000-11-17	7955.46

To calculate this amount, you use the GROUP command. Inside each iteration of GROUP, you:

1. Calculate the running total as of the current record.
2. Extract the invoice number, amount, date, and running total to a results table.

OPEN Ap_Trans
			
COMMENT set the initial value of running total to zero END
ASSIGN v_running_total = 0.00

COMMENT iterate over each record in the table and then calculate and extract the running total END
GROUP
  ASSIGN v_running_total = v_running_total + Amount
  EXTRACT Invoice_Number, Amount, Date, v_running_total AS "Running total" TO results1
END

When the script runs, the commands inside the GROUP block are processed against each record in the table, from top to bottom, and the running total is calculated and extracted. If we could walk through GROUP as it runs, this is how it would look:

First iteration of GROUP: running total = 0.00 + 618.30

The GROUP adds the invoice amount of the first record to the initial running total of 0.00 and extracts the fields to the results table:

Vendor_Number	Vendor_Name	Invoice_Number	Date	Amount
11663	More Power Industries	5981807	2000-11-17	618.30
13808	NOVATECH Wholesale	2275301	2000-11-17	6705.12
12433	Koro International	6585673	2000-11-17	7955.46

Second iteration of GROUP: running total = 618.30 + 6705.12

The GROUP block adds the invoice amount of the second record to the new running total of 618.30 and extracts the fields to the results table:

Vendor_Number	Vendor_Name	Invoice_Number	Date	Amount
11663	More Power Industries	5981807	2000-11-17	618.30
13808	NOVATECH Wholesale	2275301	2000-11-17	6705.12
12433	Koro International	6585673	2000-11-17	7955.46

Third iteration of GROUP: running total = 7323.42 + 7955.46

The GROUP block adds the invoice amount of the third record to the new running total of 7323.42 and extracts the fields to the results table:

Vendor_Number	Vendor_Name	Invoice_Number	Date	Amount
11663	More Power Industries	5981807	2000-11-17	618.30
13808	NOVATECH Wholesale	2275301	2000-11-17	6705.12
12433	Koro International	6585673	2000-11-17	7955.46

Final results table

After GROUP has processed the final record in the table, you have the following results table:

Invoice_Number	Amount	Date	Running_total
5981807	618.30	2000-11-17	618.30
2275301	6705.12	2000-11-17	7323.42
6585673	7955.46	2000-11-17	15278.88

Handling different cases using GROUP IF

Using the same AP_Trans table as above, you now need to calculate running totals for three types of invoices:

• High value (greater than or equal to 1000.00)
• Medium value (between 100.00 and 1000.00)
• Low value (less than 100.00)

The GROUP command provides an IF/ELSE structure to handle different cases. You provide the conditional expressions to test, and if a record evaluates to true, then the commands inside the block run.

How cases are tested

Cases are tested from top to bottom, and a record can only be processed by one IF/ELSE block. The first case that evaluates to true for the record is the one that processes the record:

1. When GROUP processes the first record, it tests it against the first IF condition (Amount >= 1000). If this evaluates to true, then the code for this case runs and no other cases are tested.
2. If the first case evaluates to false, then the next ELSE IF condition (Amount >= 100) is tested. Likewise, if this test evaluates to true, then the code for this case runs and no other cases are tested.
3. Finally, if none of the IF or ELSE IF cases evaluate to true, then the default case in the ELSE block processes the record.

OPEN Ap_Trans

COMMENT set initial values for running totals END
ASSIGN v_running_total_hi = 0.00
ASSIGN v_running_total_med = 0.00
ASSIGN v_running_total_low = 0.00

COMMENT use GROUP IF to run different ASSIGN and EXTRACT commands depending on invoice amount END
GROUP IF Amount >= 1000
  ASSIGN v_running_total_hi = v_running_total_hi + Amount
  EXTRACT Invoice_Number, Amount, Date, v_running_total_hi AS "Running total" TO results_hi
ELSE IF Amount >= 100
  ASSIGN v_running_total_med = v_running_total_med + Amount
  EXTRACT Invoice_Number, Amount, Date, v_running_total_med AS "Running total" TO results_med
ELSE
  ASSIGN v_running_total_low = v_running_total_low + Amount
  EXTRACT Invoice_Number, Amount, Date, v_running_total_low AS "Running total" TO results_low
END

When the script runs, the GROUP command tests the invoice amount for each record. Depending on the amount, the record is used to update one of three running totals (low, medium, high) and three result tables are produced.

LOOP inside a GROUP

When using GROUP to process the records in a table, you can use a LOOP command to execute a series of commands on a single record multiple times. LOOP is a second iteration that happens inside the iteration of GROUP, and it runs until a test condition that you specify evaluates to false.

Using LOOP to split a field

You have the following table containing invoice data and you need to isolate specific information for invoice amounts per department. One invoice may be related to more than one department, and department codes are stored in comma-delimited format in the table:

Vendor_Number	Invoice_Number	Date	Amount	Dept_Code
11663	5981807	2000-11-17	618.30	CCD,RDR
13808	2275301	2000-11-17	6705.12	CCD
12433	6585673	2000-11-17	7955.46	CCD,LMO,RDR

To extract the invoice amounts per department, you:

1. Use a GROUP command to process the table record by record.
2. Calculate the number of departments (n) associated with each record.
3. Use the LOOP command to iterate n times over the record to extract data for each department associated with the record.

 

COMMENT
use GROUP to count commas in each department code field as a way of identifying how many departments are associated with the record
"LOOP" over each record for each code in the field, with each iteration of the loop extracting the record with a single code to the result1 table
END
GROUP
  v_department_count = OCCURS(Dept_Code,',')
  v_counter = 0
  LOOP WHILE v_counter <= v_department_count
    v_dept = SPLIT(Dept_Code, ',', (v_counter + 1))
    EXTRACT FIELDS Invoice_Number, Amount, v_dept AS "Department" TO result1
    v_counter = v_counter + 1		
  END
END

When the script runs, the commands inside the GROUP block are processed against each record in the table, from top to bottom. For each record, the LOOP command iterates over the record once per department code in the comma-delimited list and then extracts a record. If we could walk through GROUP and LOOP as they run, this is how it would look:

First iteration of GROUP: 2 iterations of LOOP

Vendor_Number	Invoice_Number	Date	Amount	Dept_Code
11663	5981807	2000-11-17	618.30	CCD,RDR
13808	2275301	2000-11-17	6705.12	CCD
12433	6585673	2000-11-17	7955.46	CCD,LMO,RDR

For the first record in the table, the value of v_department_count is 1, so LOOP iterates twice:

1. For the first iteration of the LOOP:
   • v_counter = 0
   • v_depart = CCD

   The following record is extracted and the value of v_counter is incremented to 1, therefore LOOP iterates again:

   5981807

   618.30

   CCD

2. For the second iteration of LOOP:
   • v_counter = 1
   • v_depart = RDR

   The following record is extracted and the value of v_counter is incremented to 2, therefore LOOP does not iterate again and GROUP proceeds to the next record:

   5981807

   618.30

   RDR

Second iteration of GROUP: 1 iteration of LOOP

Vendor_Number	Invoice_Number	Date	Amount	Dept_Code
11663	5981807	2000-11-17	618.30	CCD,RDR
13808	2275301	2000-11-17	6705.12	CCD
12433	6585673	2000-11-17	7955.46	CCD,LMO,RDR

For the second record in the table, the value of v_department_count is 0, so LOOP iterates once:

• v_counter = 0
• v_depart = CCD

The following record is extracted and the value of v_counter is incremented to 1, therefore LOOP does not iterate again and GROUP proceeds to the next record:

2275301

6705.12

CCD

Third iteration of GROUP: 3 iterations of LOOP

Vendor_Number	Invoice_Number	Date	Amount	Dept_Code
11663	5981807	2000-11-17	618.30	CCD,RDR
13808	2275301	2000-11-17	6705.12	CCD
12433	6585673	2000-11-17	7955.46	CCD,LMO,RDR

For the third record in the table, the value of v_department_count is 2, so LOOP iterates three times:

1. For the first iteration of LOOP:
   • v_counter = 0
   • v_depart = CCD

   The following record is extracted and the value of v_counter is incremented to 1, therefore LOOP iterates again:

   6585673

   7955.46

   CCD

2. For the second iteration of LOOP:
   • v_counter = 1
   • v_depart = LMO

   The following record is extracted and the value of v_counter is incremented to 2, therefore LOOP iterates again:

   6585673

   7955.46

   LMO

3. For the third iteration of LOOP:
   • v_counter = 2
   • v_depart = RDR

   The following record is extracted and the value of v_counter is incremented to 3, therefore LOOP does not iterate again and GROUP reaches the end of the table:

   6585673

   7955.46

   RDR

Final results table

After GROUP has processed each record in the table, and LOOP has iterated though all the department codes, you have the following results table:

Invoice_Number	Amount	Department
5981807	618.30	CCD
5981807	618.30	RDR
2275301	6705.12	CCD
6585673	7955.46	CCD
6585673	7955.46	LMO
6585673	7955.46	RDR