Gap Detection on a Date Document Number Combo Field

Assume you have a data set with a character field titled 'Document Number' which has the following sample values -

 

2015100312345

2015100312346

2015100312348

2015100412345

2015100412347

2015100412348

 

A closer look at the sample values mentioned reveal a pattern. The 'Document Number' is a combination of date and document number. So 2015100312345 is date - 3rd October 2015 (YYYYMMDD) and document number - 12345

 

Now if you are required to identify the missing document number/s within each date (not across dates since the document number starts afresh for each date) you have to apply Gap Detection in IDEA very skilfully.

 

In the Gap Detection dialog box select Field for Gap Detection as 'Document Number'. No sooner you select 'Document Number' as the field the Mask would need to be changed to CCCCCCCCNNNNN. In the Mask CCCCCCCC represents the date i.e. 20151003 from the example above and NNNNN represents the actual sequential document number i.e. 12345 from the example above.

 

By applying the Gap Detection with this Mask you will get the following gaps -

 

2015100312347

 

2015100412346

 

Kind Regards

 

Jairam