About gaps

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. You can test numeric or datetime fields, or numbers in character fields. You can test only one field at a time. 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 have two options when outputting the results of gaps testing:

Testing numeric data for gaps

When you test numeric data for gaps, the number of decimal places governs the allowable interval in the data:

Table 1. Examples of testing numeric data for gaps

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)

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:

Table 2. Examples of testing dates and datetimes for gaps

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)

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, cheque 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.

Table 3. Examples of testing numbers in a character field for gaps

Test values

Missing items

Number of missing items

A123

C124

0 (character number)

A123

B125

124

1 (character number)

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 ACL 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 examples that follow, 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.

Table 4. Examples of incorrect gaps results

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)

If you suspect an anomaly exists, sort 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, to ensure valid results you need to use an ACL function such as INCLUDE( ) to strip out the letters before testing for gaps.

Related tasks
Testing for gaps


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