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 Analytics require that data is 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.

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.

Testing sequential order does not sort records

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 for out-of-sequence items

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.

The sort sequence for testing character data

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 Analytics – 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, Analytics 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.

SEQUENCE ON Date Amount D

SEQUENCE ON Amount D Date

SEQUENCE ON Date Amount

Steps

You can use the Examine Sequence option to determine if one or more fields in the active table are ordered sequentially, or to identify out-of-sequence items.

Show me how

1. Select Analyze > Sequence.
2. On the Main tab, do one of the following:

   Select the field(s) to test from the Sequence On list.

   Click Sequence On to select the field(s), or to create an expression.

   If you select more than one field, the order in which you select the fields dictates the testing priority. The records are tested by the first field you select, and if there are multiple sequential occurrences of the same value in the first field, the records within the group are then tested by the second field you select, and so on. If you do not select additional fields, records within a group are not subject to any secondary testing.

   Note

   When testing tables sorted or indexed by more than one field (nested sorting or indexing), testing priority needs to match sorting or indexing priority (primary key field, secondary key field, and so on) for results to be valid.

   The order in which you select the fields is the order in which the columns appear in the results.

3. If you clicked Sequence On, you can optionally specify a descending sort order for one or more selected fields by clicking the sort arrow (the default is ascending).

   Note

   When testing fields that have been previously sorted or indexed, the direction of the sort order you specify – ascending or descending – must match the direction of the field you are testing for results to be valid.

4. If there are records in the current view that you want to exclude from processing, enter a condition in the If text box, or click If to create an IF statement using the Expression Builder.

   Note

   The If condition is evaluated against only the records remaining in a table after any scope options have been applied (First, Next, While).

   The IF statement considers all records in the view and filters out those that do not meet the specified condition.

5. Click the Output tab.
6. Select the appropriate output option in the To panel:
   • Screen – Select this option to display the results in the Analytics display area.

     Tip

     You can click any linked result value in the display area to drill down to the associated record or records in the source table.

     If the output table contains a large number of records, it is faster and more useful to save the results to a file than to display the results on the screen.

   • Print – Select this option to send the results to the default printer.
   • Graph – Select this option to create a graph of the results and display it in the Analytics display area.
   • File – Select this option to save or append the results to a text file. The file is saved outside Analytics.

   Note

   Output options that do not apply to a particular analytical operation are disabled.

7. If you selected File as the output type, specify the following information in the As panel:
   • File Type – ASCII Text File or Unicode Text file (depending on which edition of Analytics you are using) is the only option. Saves the results to a new text file, or appends the results to an existing text file.
   • Name – Enter a file name in the Name text box. Or click Name and enter the file name, or select an existing file in the Save or Save File As dialog box to overwrite or append to the file. If Analytics prefills a file name, you can accept the prefilled name, or change it.

     You can also specify an absolute or relative file path, or navigate to a different folder, to save or append the file in a location other than the project location. For example: C:\Results\Output.txt or Results\Output.txt.

   • Local – Disabled and selected. Saving the file locally is the only option.
8. Depending on the output type, you can optionally specify a Header and/or a Footer in the text box(es).

   Headers and footers are centered by default. Type a left angle bracket (<) before the header or footer text to left align the text. Click Header or Footer to enter a header or footer of more than one line. Alternatively, you can enter a semi-colon (;) as a line-break character in the header or footer text box. Left aligning multiple lines requires a left angle bracket at the beginning of each line.

9. Click the More tab.

10. Select the appropriate option in the Scope panel:

    • All
    • First
    • Next
    • While
    Show me more

    All	This option is selected by default. Leave it selected to specify that all records in the view are processed.
    First	Select this option and enter a number in the text box to start processing at the first record in the view and include only the specified number of records.
    Next	Select this option and enter a number in the text box to start processing at the currently selected record in the view and include only the specified number of records. The actual record number in the leftmost column must be selected, not data in the row.
    While	Select this option to use a WHILE statement to limit the processing of records in the view based on a particular criterion or set of criteria. You can enter a condition in the While text box, or click While to create a WHILE statement using the Expression Builder. A WHILE statement allows records in the view to be processed only while the specified condition evaluates to true. As soon as the condition evaluates to false, the processing terminates, and no further records are considered. You can use the While option in conjunction with the All, First, or Next options. Record processing stops as soon as one limit is reached.
    Note The number of records specified in the First or Next options references either the physical or the indexed order of records in a table, and disregards any filtering or quick sorting applied to the view. However, results of analytical operations respect any filtering. If a view is quick sorted, Next behaves like First.

11. In the Error Limit field, specify the maximum number of non-sequential items to list, or keep the default of 10.

    If the limit is reached, Analytics stops processing and outputs the non-sequential items to that point.The Error Limit number applies to the combined number of errors across all fields being tested. It is not a per-field limit.

12. If you selected File as the output type, and want to append the output results to the end of an existing text file, select Append To Existing File.
13. Click OK.
14. If the overwrite prompt appears, select the appropriate option.