GAPS command

Concept Information

Detects whether a numeric or datetime field in an Analytics table contains one or more gaps in sequential data.

Syntax

GAPS <ON> key_field <D> <UNFORMATTED> <PRESORT> <MISSING limit> <HEADER header_text> <FOOTER footer_text> <IF test> <WHILE test> <FIRST range|NEXT range> <TO {SCREEN|table_name|filename|PRINT}> <LOCAL> <APPEND> <OPEN>

Parameters

Name	Description
ON key_field D	The field or expression to check for gaps. Include D to sort the key field in descending order. The default sort order is ascending.
UNFORMATTED optional	Suppresses page headings and page breaks when the results are output to a file.
PRESORT optional	Sorts the table on the key field before executing the command. Note You cannot use PRESORT inside the GROUP command.
MISSING limit optional	The output results contain individual missing items rather than gap ranges. The limit value specifies the maximum number of missing items to report for each identified gap. The default value is 5. If the limit is exceeded for a particular gap, the missing items are reported as a range for that particular gap. The limit value does not restrict the total number of missing items reported, it only restricts the number of missing items reported within a specific gap.
HEADER header_text optional	The text to insert at the top of each page of a report. header_text must be specified as a quoted string. The value overrides the Analytics HEADER system variable.
FOOTER footer_text optional	The text to insert at the bottom of each page of a report. footer_text must be specified as a quoted string. The value overrides the Analytics FOOTER system variable.
IF test optional	A conditional expression that must be true in order to process each record. The command is executed on only those records that satisfy the condition. Note The IF parameter is evaluated against only the records remaining in a table after any scope parameters have been applied (WHILE, FIRST, NEXT).
WHILE test optional	A conditional expression that must be true in order to process each record. The command is executed until the condition evaluates as false, or the end of the table is reached. Note If you use WHILE in conjunction with FIRST or NEXT, record processing stops as soon as one limit is reached.
FIRST range \| NEXT range optional	The number of records to process: FIRST start processing from the first record until the specified number of records is reached NEXT start processing from the currently selected record until the specified number of records is reached Use range to specify the number of records to process. If you omit FIRST and NEXT, all records are processed by default.
TO SCREEN \| table_name \| filename \| PRINT optional	The location to send the results of the command to: SCREEN displays 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. table_name saves the results to an Analytics table Specify table_name as a quoted string with a .FIL file extension. For example: TO "Output.FIL" By default, the table data file (.FIL) is saved to the folder containing the Analytics project. Use either an absolute or relative file path to save the data file to a different, existing folder: TO "C:\Output.FIL" TO "Results\Output.FIL" Note Table names are limited to 64 alphanumeric characters, not including the .FIL extension. The name can include the underscore character ( _ ), but no other special characters, or any spaces. The name cannot start with a number. filename saves the results to a file Specify filename as a quoted string with the appropriate file extension. For example: TO "Output.TXT" By default, the file is saved to the folder containing the Analytics project. Use either an absolute or relative file path to save the file to a different, existing folder: TO "C:\Output.TXT" TO "Results\Output.TXT" PRINT sends the results to the default printer
LOCAL optional	Saves the output file in the same location as the Analytics project. Note Applicable only when running the command against a server table with an output file that is an Analytics table. The LOCAL parameter must immediately follow the TO parameter.
APPEND optional	Appends the command output to the end of an existing file instead of overwriting it. Note You must ensure that the structure of the command output and the existing file are identical: the same fields the same field order matching fields are the same length matching fields are the same data type Analytics appends output to an existing file regardless of its structure. If the structure of the output and the existing file do not match, jumbled, missing, or inaccurate data can result.
OPEN optional	Opens the table created by the command after the command executes. Only valid if the command creates an output table.

Analytics output variables

Name	Contains
GAPDUPn	The total number of gaps, duplicates, or fuzzy duplicate groups identified by the command.

Examples

Testing for missing invoice numbers

You use GAPS to ensure that there are no invoice numbers missing from an Invoices table:

OPEN Invoices
GAPS ON Inv_Num PRESORT TO "Invoices_Gaps.fil"

Remarks

For more information about how this command works, see Testing for gaps.

Using GAPS on character fields

In addition to testing numeric or datetime fields, you can also test for gaps in numeric data that appears in a character field. For example, you can test check numbers, which are typically formatted as character data.

If letters and numbers appear together in a character field, only the numbers are tested and the letters are ignored.