Skip to main content

Gap Detection on a Date Document Number Combo Field

Hi,

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