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