About testing sequential order

Testing sequential order (the Examine Sequence option) allows you to verify whether data has already been sorted or indexed, or whether it needs to be before you perform certain analytical tests, or data combining operations. Several tests and operations in ACL require data be in sequential order for the results to be valid, or for the operation to execute successfully. Instead of unnecessarily sorting or indexing a table, you can first test it to find out if sorting or indexing is required. Testing first can save time, as sorting especially can require considerable time and system resources with large tables.

Testing sequential order does not sequentially order or sort records, or in any way modify the order of records in the table being tested. It verifies whether or not specified fields in a table are currently sequentially ordered, and reports any errors of sequence. To sort or sequentially order records requires performing separate sorting or indexing operations.

Testing sequential order also allows you to identify out-of-sequence items in data that should have an inherent sequential order, such as invoice or check numbers, indicating possible irregularities. For example, you could sort the invoice data for a particular vendor by date, and then test the sequential order of the invoice numbers. Out-of-sequence invoice numbers could warrant further scrutiny.

You can test the sequential order of character, numeric, datetime, or computed fields, or a combination of fields and data types if the data is sorted or indexed by more than one field.

The sequential order of character fields is tested against whatever sort sequence is specified for character data in the Sort Order option (Tools > Options > Table). Typically the default sort sequence is specified (0,1,2... A,B,C...) unless you have changed it. Some sorting subtleties exist in the non-Unicode edition of ACL – for example, the default sort sequence dictates that all uppercase alpha characters are sorted before all lowercase alpha characters.

How sequence errors are reported

The Examine Sequence option compares the first value in a column against the second value, the second value against the third, and so on, progressing down the column by comparing paired values. A sequence error is reported if a pair of values constitutes a break in the sequence. Following a break, the sequence begins anew, using the second of the paired values as the new starting point. Any values after the break that are out of sequence when compared to values before the break are not reported as sequence errors. For example, when testing the following column of values for ascending order, ACL reports two sequence errors (4, 1), not five (4, 4, 5, 1, 2).

 
1
3
6
4    sequence error
4
5
6
9
1    sequence error
2

Testing nested sort orders

If you are testing the sequential order of two or more fields in combination, for the results to be valid, you need to select the fields for testing in the same order of priority as the sort order or index order priority – primary key field, secondary key field, and so on. You also need to match the direction of the sequential order – ascending or descending – for each field. The following examples demonstrate results that are valid and invalid based on order of priority and direction.

Table 1. Valid and invalid results when testing nested sort orders

Date field

(ascending)

Amount field

(descending, nested)

Sequence On

Date Amount D

Sequence On

Amount D Date

Sequence On

Date Amount

15 Jan 2011

15 Jan 2011

15 Jan 2011

16 Jan 2011

16 Jan 2011

17 Jan 2011

17 Jan 2011

17 Jan 2011

$2300.00

$1200.00

 $600.00

 $900.00

 $100.00

$4700.00

 $900.00

 $500.00

0 sequence errors detected

valid result – sequence test uses same order of priority and direction as fields being tested

2 sequence errors detected

invalid result – sequence test uses different order of priority from fields being tested, treats Amount field as unnested

5 sequence errors detected

invalid result – sequence test uses different direction from one of the fields being tested, treats Amount field as sorted ascending

     

Related concepts
Testing sequential order, sorting, and indexing
About the Sort Order option and sort sequences
Sorting versus indexing
About sorting
About indexing
Sorting and indexing using computed fields
Related tasks
Testing sequential order


(C) 2015 ACL Services Ltd. All Rights Reserved.