Find the Missing Number

[Keith Hubert]

Hi All,

I have a list of numbers formatted as characters. The list should be complete but for some unknown reason some numbers are missing.

I would like some help in building a script to read through the list and report all the missing numbers.

eg
1
3

2 is clearly missing. My file has over 25,000 records, hence the need for a script.

All help as always is appreciated.

[MikeC]

Re: Find the Missing Number

Hi Keith,
Maybe something like this

dim lst as c
dim cnt as n
dim missing as c
dim x as n
x=1
missing=""
lst=<<%a%
1
3
4
5
8
9
11
%a%
cnt=*max(lst)
for i=1 to cnt
 if word(lst,x,crlf())<> alltrim(str(i))
   missing=missing+alltrim(str(i))+crlf()
  else
   x=x+1
 end if
next

?missing
= 2
6
7
10

[Keith Hubert]

Re: Find the Missing Number

Hi Mike,

Thanks for script, looks like it will do the job, how do I use it against a field in a table?

[Mark Pearson]

Re: Find the Missing Number

Not really having experienced xbasic yet, but I would be trying loop through the records. When the number does not match, put the non matching number in an array.

In this case I would loop through starting from 1. Dim i as n

i = i +1

When i does note equal the record number, there is a mismatch. Put i into the array.

You should end up with an array populated with all the number i failed to match.

The big issue I see is that your field is not numeric and unless it is a fixed length with preleading 0 (EG 0000001, 0000002 etc), then it will not sort sequentially for the above to work accurately. You could overcome this by first updating the records with a function that fixes the length by adding the 0s to the beginning, taking note to reduce the number of 0 everytime the character length is increased by 1 (EG: 1, 11, 100, 1000). It can then be sorted sequentially

[G Gabriel]

Re: Find the Missing Number

You do not need a script.
Add a field to the table and make it a calc one.
New_field=if(val(field)-val(previous(field))>1,"Missing","")
Recalc the table.
Then filter the table for missing.

If you want a script, you can create a list with the proper sequence then create a list from the existing table and use:
WORD_SUBTRACT()

Here is a mock script:

[COLOR="Blue"]'First create a proper list. You know the starting and ending numbers:[/COLOR]
correct_lst="1"
x="2"
for i=1 to your_ending _number-1
correct_lst=correct_lst+crlf()+x
x=increment_value(x)
next
[COLOR="Blue"]'Get a list from the current table[/COLOR]
wrong_lst=table.external_content_get(...)
[COLOR="Blue"]'now subtract the wrong list ffrom the correct list and 
'you have a new list of the missing numbers:[/COLOR]
missing_numbers=word_subtract(correct_lst,wrong_lst)

[MikeC]

Re: Find the Missing Number

how do I use it against a field in a table?

Somewhat of a different question and does then make available additional solutions as you've noticed! :)

You could use table.external_records_get() for one way to get your field into a crlf list and use the same script I presented....or use the various other ways....

[Tom Cone Jr]

Re: Find the Missing Number

And a brute force approach is always available, but can be pretty messy.

-- tom

[Stan Mathews]

Re: Find the Missing Number

Somewhat of a different question and does then make available additional solutions as you've noticed! :)

You could use table.external_records_get() for one way to get your field into a crlf list and use the same script I presented....or use the various other ways....

I would use table.external_record_content_get() to get a list of the values in the table. Then a short script to create a list of values from 1 to the highest value found. Then word_subtract() to remove the table values list from the "whole" list. The result should be the missing numbers.

whole_list = "" 'build list of character digit representations from 1 to 25000
for i = 1 to 25000
whole_list = whole_list + alltrim(str(i))+crlf()
next i

[G Gabriel]

Re: Find the Missing Number

How about just simple cut to the chase approach:
1-Create a duplicate table with all the same fields but don't copy the records.
2-Fill the field in question with serial values: 1,2,3 etc (using update operation).
3-Update the new table (master table) with the old table (transaction table) using this field for matching.

The missing numbers will have records with these missing numbers, but all other fields will be blank.

[Keith Hubert]

Re: Find the Missing Number

Guys you are just the best.

Many thanks for your great suggestions.

[Mike Wilson]

Re: Find the Missing Number

OK Keith,
Which did you end up using?? :)

[Keith Hubert]

Re: Find the Missing Number

Hi Mike,

It was a mixture of everything, but Tom C. pointed out that I had misled every one by describing my 25000 numbers as a list instead of records in a table.

After that, he gallantly requested the table and he wrote the code that wrote the missing numbers to the clipboard. I created filtered by year records and ran the script against those records. I was then able to paste the result to the note pad and print.

With the result, I can see there has been a problem of loosing job numbers for a number of years. Even before I came on the scene.

Mike C has helped to redo the increment job number routine and also Tom C. has reminded me about the increment routine Cal developed. I shall have another look at that too.

[Keith Hubert]

Re: Find the Missing Number

Here is the sample Tom Cone Jnr. very kindly created for me, and gave permission for me to post for other users to learn from.