Gaps in Sequences
On the ACL User Forum a person asked how to identify a gap within a specific sequence. In other words, suppose that you have a field "TYPE" and within the field "TYPE" you want to know if the invoices are sequential. This code will identify gaps within the sequence.
OPEN table
SORT ON type invoice TO TEMP1 OPEN
v_type = blank(30)
v_inv_num = 0
GROUP
GROUP IF v_inv_num +1 <> invoice AND v_type = type
EXTRACT v_type v_inv_num invoice to temp3
END
v_inv_num = invoice
v_type = group
END
Given the data
Invoice Type
11111 AAA
11112 AAA
11113 AAA
11115 AAA
11116 AAA
22221 BBB
22222 BBB
22223 BBB
22225 BBB
22228 BBB
22230 BBB
22231 BBB
22232 BBB
33331 CCC
33333 CCC
33335 CCC
33336 CCC
33338 CCC
33339 CCC
The results will look like:
TYPE INV1 INV2
AAA 11113 11115
BBB 22223 22225
BBB 22225 22228
BBB 22228 22230
CCC 33331 33333
CCC 33333 33335
CCC 33336 33338