Testing for gaps
Concept Information
Gaps in sequentially ordered numeric or datetime fields could indicate a data file is incomplete. You can test for gaps in sequentially ordered values in a field, and identify one or more gaps or missing items, if they exist.
For results to be valid, the field being tested must be in sequential order prior to testing. You can sort a field in advance, or use the Presort option during the gaps test.
You can test numeric or datetime fields, or numbers in character fields. You can test only one field at a time.
List gap ranges or list missing items
You have two options when outputting the results of gaps testing:
- List Gap Ranges
- List Missing Items
List Gap Ranges
This option identifies the start and end points of a gap, and the total number of missing items in the gap.
The values that identify the start and end points of the gap are not missing items themselves. They are the sequential values that appear immediately before and immediately after the gap – labeled Gap Start (Exclusive) and Gap End (Exclusive). For example, check numbers 12345 and 12350 would identify a gap with four missing items between those two numbers.
List Missing Items
This option lists the individual missing items in a gap. For example, check numbers 12346, 12347, 12348, and 12349.
These missing items are calculated values and do not actually appear in the data being tested. When using this option, the Maximum Missing Items field allows you to specify the maximum number of missing items to list individually for each gap, which is useful if a gap is large. If the maximum is exceeded, Analytics uses the range method of identifying gaps instead, with the one difference that the values identifying the start and end points of the gap are the first and last missing items, sequentially – labeled Gap Start (Inclusive) and Gap End (Inclusive).
Note
The number you specify in Maximum Missing Items applies on a per-gap basis. It does not limit the total number of missing item results across a data set, whether listed individually or by range.
When using the missing items method, the results can contain a mix of individual missing items and ranges depending on the value in the Maximum Missing Items field and the size of the different gaps.
Testing numeric data for gaps
When you test numeric data for gaps, the number of decimal places in the data governs the allowable interval in the data:
- numeric data contains only integers (no decimal portion) the allowable interval is 1
An interval greater than 1 is a gap. For gaps reported as ranges, the number of missing items is the number of missing integers.
- numeric data contains decimal places the allowable interval is equivalent to the smallest decimal interval
For example, if a numeric field has two decimal places, the allowable interval is 0.01. An interval greater than the smallest decimal interval is a gap. For gaps reported as ranges, the number of missing items is the number of missing decimal intervals.
Examples of testing numeric data for gaps
In the first example the numeric data contains only integers. The allowable interval is 1.
Test values |
Missing items |
Number of missing items |
---|---|---|
-2 -1 0 1 4 5 15 |
2 3 6 (to) 14 (Inclusive) |
1 (integer) 1 (integer) 9 (integers) |
In the second example the numeric data contains two decimal places. The allowable interval is 0.01.
Test values |
Missing items |
Number of missing items |
---|---|---|
4.24 4.25 4.26 4.29 4.30 5.00 |
4.27 4.28 4.31 (to) 4.99 (Inclusive) |
1 (0.01 interval) 1 (0.01 interval) 69 (0.01 intervals) |
Testing datetime data for gaps
You can test date, datetime, or time data for gaps:
- The allowable interval in date fields is one day
An interval greater than one day is a gap. For gaps reported as ranges, the number of missing items is the number of missing days.
- The allowable interval in datetime or time fields is one second
An interval greater than one second is a gap. For gaps reported as ranges, the number of missing items is the number of missing seconds. So a gap of one hour would be reported as a range with 3,600 missing items, and a gap of one day would be reported as a range with 86,400 missing items.
Examples of testing dates and datetimes for gaps
In the first example the data contains only dates. The allowable interval is one day.
Test values |
Missing items |
Number of missing items |
---|---|---|
27 Dec 2014 28 Dec 2014 31 Dec 2014 01 Jan 2015 02 Jan 2015 12 Jan 2015 13 Jan 2015 |
29 Dec 2014 30 Dec 2014 03 Jan 2015 (to) 11 Jan 2015 (Inclusive) |
1 (day) 1 (day) 9 (days) |
In the second example the data contains datetimes. The allowable interval is one second.
Test values |
Missing items |
Number of missing items |
---|---|---|
31 Dec 2014 23:59:54 31 Dec 2014 23:59:55 31 Dec 2014 23:59:58 31 Dec 2014 23:59:59 01 Jan 2015 01:00:00 01 Jan 2015 01:00:01 02 Jan 2015 01:00:02 |
31 Dec 2014 23:59:56 31 Dec 2014 23:59:57 01 Jan 2015 00:00:00 (to) 01 Jan 2015 00:59:59 (Inclusive) 01 Jan 2015 01:00:02 (to) 02 Jan 2015 01:00:01 (Inclusive) |
1 (second) 1 (second) 3,600 (seconds) 86,400 (seconds) |
Testing numeric data in a character field for gaps
You can test for gaps in numeric data that appears in a character field – for example, 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.
Examples of testing numbers in a character field for gaps
Note how the alpha prefixes are ignored, and only the numbers are considered.
Test values |
Missing items |
Number of missing items |
---|---|---|
A123 C124 |
|
0 (character number) |
A123 B125 |
124 |
1 (character number) |
Sorting of character fields can affect gaps testing
Depending on the arrangement of letters and numbers in character field values, anomalies may exist among the results of gaps testing. For example, if some numbers are prefaced with a letter and some are not, or in the non-Unicode edition of Analytics if some preceding letters are lowercase and some are uppercase, results may not be accurate.
The reason for the inaccuracy is that the inconsistent presence of alpha characters, or the inconsistent case of alpha characters, prevents the numbers being fully sequentially ordered by the Presort option. In the table below, 126 and 127, and 124, are not actually missing items, but because of the way the alphanumeric strings are sorted they are returned as missing items.
If you suspect an anomaly exists, perform a separate sort operation on the field in question to reveal the sequence of character field values being tested for gaps. If sequential numeric order is being disrupted by the presence of letters, you can ensure valid results by using an Analytics function such as INCLUDE( ) to strip out the letters before testing for gaps.
Examples of incorrect gaps results
Note how the inconsistent presence of alpha characters, or the inconsistent case of alpha characters, is causing items to be incorrectly reported as missing.
Test values |
Missing items |
Number of missing items |
---|---|---|
123 124 125 128 129 A-126 A-127 |
126 127 |
1 (character number) 1 (character number) |
A-123 a-124 A-125 A-128 A-129 A-126 A-127 |
124 |
1 (character number) |
Steps
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.
Select the fields
- In the Navigator, open the table that you want to test for gaps.
- Select Analyze > Gaps.
- On the Main tab, do one of the following:
From the Gaps On list, select a field to test.
Click Gaps On to select a field, or to create an expression.
You can test only one field at a time.
- Optional. If you clicked Gaps On, click the sort arrow beside the selected field to specify a descending sort order in the output results (the default is ascending).
Exclude records from processing (optional)
-
If there are records in the current view that you want to exclude from processing, do one of the following:
-
Enter a condition in the If text box
-
Click If to create an IF statement using the Expression Builder
The If condition considers all records in the view and filters out those that do not meet the specified condition.
Note
The If condition is evaluated against only the records remaining in a table after any scope options have been applied (First, Next, While).
-
Deselect Presort (optional)
-
If the test field is already sorted based on a previous operation, deselect Presort to save time when testing large tables for gaps.
If data in the test field is not sorted, you must leave Presort selected to ensure that all gaps are found.
Note
If you deselect Presort, the test field 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 Analytics table, the warning message appears in the command log.
Output missing ranges or missing items
-
Under Output Type, select one of the following:
-
List Gap Ranges identifies the start and end points of gaps, and the total number of individual missing items in a gap.
-
List Missing Items lists 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.
-
Configure the output
- Click the Output tab.
-
In the To panel, select the appropriate output option.
Note
Output options are disabled is they do not apply to a particular analytical operation.
Option Details 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.
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 Sends the results to the default printer. Graph Creates a graph of the results and display it in the Analytics display area. File Saves or appends the results to an Analytics table or a text file, or overwrites an existing table or file.
- Analytics table the table is added to the open project if it is not already in the project.
- Text file the file is saved outside Analytics.
-
If you selected File as the output type, specify the appropriate information in the As panel.
Option Details File Type -
Analytics Table – saves or appends the results to an Analytics table, or overwrites an existing table.
-
ASCII Text File or Unicode Text file – saves or appends the results to a text file, or overwrites an existing file.
Name -
To save to a new table or file
Enter a file name in the Name text box. If Analytics prefills a file name, you can accept the prefilled name, or change it.
-
To append to an existing table or file, or to overwrite an existing table or file
Click Name and select an existing table or file in the Save or Save File As dialog box.
You can specify an absolute or relative file path, or navigate to a different folder, to save, append, or overwrite in a location other than the project location. For example:
C:\Results\Output.fil or Results\Output.fil.
Note
Analytics 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.
Local Enabled only when connected to a server table and saving, appending, or overwriting to an Analytics table.
-
Select Local saves, appends, or overwrites to the same location as the Analytics project.
-
Leave Local deselected saves, appends, or overwrites to the Prefix folder on a server.
Note
For output results produced from analysis or processing of AX Server tables, select Local. You cannot deselect the Local setting to import results tables to AX Server.
-
-
Optional. Specify a Header or a Footer in the appropriate text box.
Click Header or Footer to enter a header or footer of more than one line. You can also enter a semi-colon (;) as a line-break character in the header or footer text box.
Headers and footers are centered by default. Type a left angle bracket (<) before the header or footer text to left align the text. Left aligning multiple lines requires a left angle bracket at the beginning of each line.
Note
Specifying a header or a footer is not available for some output types.
Specify the scope of the operation
- Click the More tab.
-
In the Scope panel, select the appropriate option:
- All
- First
- Next
- While
Show me moreScope option Details All (Default) Specifies that all records in the view are processed. First Enter a number in the text box. Starts processing at the first record in the view and includes only the specified number of records. Next Enter a number in the text box. Starts processing at the currently selected record in the view and includes only the specified number of records. The actual record number must be selected in the leftmost column in the view, not data in the row. While Use a WHILE statement to limit the processing of records in the view based on a particular criterion or set of criteria.
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 First or Next options reference either the physical order or the indexed order of records in a table. First or Next disregard any filtering or quick sorting applied to a table view. However, output results of analytical operations respect any filtering.
If a view is quick sorted, Next behaves like First.
Finalize the settings and output the results
-
Optional. If you selected File as the output type, and you want to append the output results to the end of an existing table or file, do one of the following:
-
Select Append To Existing File if you are certain that the output results and the table or file are identical in structure.
Selecting Append To Existing File forces the append to happen even if the record lengths of the output results and an existing table or file are different. If the record lengths are different, the data structure is not identical, and the append will not work correctly.
-
Leave Append To Existing File deselected if you want Analytics to compare the record lengths of the output results and an existing table. This automated check is available only for Analytics tables, not text files.
Note
Leave Append To Existing File deselected if you are uncertain whether the output results and the existing table have an identical data structure. For more information about appending and data structure, see Appending output results to an existing table.
-
-
If you selected File (Analytics Table) as the output type, select Use Output Table if you want the output table to open automatically upon completion of the operation.
- Click OK.
- 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 output results to an existing table.