Testing for gaps

You can test a single field at a time in the active table to detect whether sequentially ordered numbers or datetime values contain any gaps.

To test for gaps:

  1. Select Analyze > Look for Gaps.
  2. On the Main tab, do one of the following:
    • Select a field to test from the Gaps On list.

    • Click Gaps On to select the field, or to create an expression.

    You can test only one field at a time.

  3. If you clicked Gaps On, you can optionally specify a descending sort order in the output results for the selected field by clicking the sort arrow (the default is ascending).
  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.

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

  5. If the field is already sorted based on a prior operation, you can optionally deselect Presort to save time when testing large tables for gaps.

    If data in the field is not sorted you must leave Presort selected to ensure that all gaps are found.

    Note

    If you deselect Presort, the field you select for gaps testing must have been previously sorted for results to be valid. The message Warning: File out of sequence accompanies results if you test an unsorted field. If you output results to an ACL table, the warning message appears in the command log.

  6. Do one of the following:
    • Select List Gap Ranges to identify the start and end points of gaps, and the total number of individual missing items in a gap.

    • Select List Missing Items to list the individual missing items in gaps. In the Maximum Missing Items field, specify the maximum number of missing items to list individually for each gap, or keep the default of 5.

  7. Click the Output tab.
  8. Select the appropriate output option in the To panel:
    • Screen – Select this option to display the results in the ACL display area. 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 ACL display area.

    • File – Select this option to save or append the results to an ACL table or a text file. If you save or append to an ACL table, the table is added to the open project if it is not already in the project. If you save or append to a text file, the file is saved outside ACL.

    Note

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

  9. If you selected File as the output type, specify the following information in the As panel:
    • File Type – Select ACL Table to save the results to a new ACL table, or append the results to an existing ACL table. Select ASCII Text File or Unicode Text file (depending on which edition of ACL you are using) to save or append the results to a 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 ACL 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.fil or Results\Output.fil.

    • Local – Only enabled when connected to a server table and saving or appending the results to an ACL table. Select Local to save the file to the same location as the project, or to specify a path or navigate to a different local folder. Leave Local deselected to save the file to the Prefix folder on the ACL Server.

      Note

      For output results produced from analysis or processing of ACL Analytics Exchange server tables, select Local. You cannot use the Local setting to import results tables to ACL Analytics Exchange Server.

  10. 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.

  11. Click the More tab.
  12. Select the appropriate option in the Scope panel.
    • All – This option is selected by default. Leave it selected to specify that all records in the view will be 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.

    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.

  13. If you selected File as the output type, and want to append the output results to the end of an existing file, do one of the following:
    • Select Append To Existing File if you are appending to a text file, or to an ACL table that you are certain is identical in structure to the output results.

    • Leave Append To Existing File deselected if you are appending to an ACL table and you want ACL to compare the record lengths of the output results and the existing table. If the record lengths are not identical, the data structure is not identical.

    Note

    Leaving Append To Existing File deselected is recommended if you are uncertain about the data structure. For more information about appending and data structure, see Appending results to ACL tables and text files.

  14. If you selected File (ACL Table) as the output type, select or deselect Use Output Table depending on whether or not you want the ACL table containing the output results to open automatically upon completion of the operation.
  15. Click OK.
  16. If the overwrite prompt appears, select the appropriate option.

    If you are expecting the Append option to appear and it does not, click No to cancel the operation and see Appending results to ACL tables and text files.

Related concepts
Saving results and specifying results output folders
About gaps


(C) 2013 ACL Services Ltd. All Rights Reserved. | Send feedback