List Out Missing Numbers of a Number Range

[dylim]

Hi Guys,

Me again. I need to show the user which invoice numbers are missing given a number range.

Suppose the following:

Actual Invoice Numbers:   1,2,3,4,5,8,9,11,12,13,15   (each of these numbers are in their respective invoice records)
Number Range: 1 to 15                                 (passed as a parameter, 2 values)

Missing numbers should be: 6,7,10,14

My knee jerk algorithm would be to:

1. Execute a SELECT statement to get the actual numbers within the range into a cursor.
2. LOOP thru the Number Range and do a SEEK or LOCATE against the Actual Invoice Numbers.

Perhaps the experts can chime in on this?

Thanks in advance. Long Live the Fox!

 

[Chriss Miller]

In general: Google, obviously. Just search whatever you search and add VFP as keyword in front.

Tamar's site: http://www.tomorrowssolutionsllc.com/
Doug Hennigs blog: https://doughennig.blogspot.com/
Rick Strahl: https://weblog.west-wind.com/
Rick Schummer: http://www.rickschummer.com/blog/
Woody: https://woody-prolib.blogspot.com/

Well, again, just google your favorite VFP MVPS, many of them have a website to look into.
But other MVPs (most valuable posters, not the same as Microsoft Valuable Professional) here have blogs, like Mike Lewis http://www.ml-consult.co.uk/
With exceptions, like me. Not interested in maintaining a site and writing content.

 

[dylim]

In general: Google, obviously. Just search whatever you search and add VFP as keyword in front.

Tamar's site: http://www.tomorrowssolutionsllc.com/
Doug Hennigs blog: https://doughennig.blogspot.com/
Rick Strahl: https://weblog.west-wind.com/
Rick Schummer: http://www.rickschummer.com/blog/
Woody: https://woody-prolib.blogspot.com/

Well, again, just google your favorite VFP MVPS, many of them have a website to look into. With exceptions, like me. Not interested in maintaining a site and writing content.
But other MVPs (most valuable posters, not the same as Microsoft Valuable Professional) here have blogs, like Mike Lewis http://www.ml-consult.co.uk/

Ah yes. Been to Doug Hennigs', Rick Strahl's and Mike Lewis'.

 

[mjcmkrsr]

Hi,

Just for fun : sketch of code to show the correct values, the missing values and duplicate values in a given range.

*!*    good values, missing values and duplicates

PUBLIC goFormSales

goFormSales=NEWOBJECT("clsForm")
goFormSales.Show

Read Events

Close all
Clear All

RETURN


**************************************************
DEFINE CLASS clsForm AS form
    AutoCenter = .T.
    Caption = "The good, the missing and the dupes"
    Width = 660
    Height = 420
    MinHeight = This.Height
    MinWidth = This.Width
    MaxWidth = This.Width
 
    ADD OBJECT grdValues AS grid WITH ;
        Left = 12, ;
        Top = 72, ;
        Width = 300, ;
        Height = ThisForm.Height - 84, ;
        TabIndex = 4, ;
        DeleteMark = .F., ;
        RecordMark = .F., ;
        ColumnCount = -1, ;
        RecordSource = "csrValues", ;
        Anchor = 15, ;
        ReadOnly =.T.
 
        PROCEDURE grdValues.Init
             WITH This.Column1
                .Header1.Caption = "ID"
             ENDWITH

             WITH This.Column2
                .Header1.Caption = "Description"
             ENDWITH

         ENDPROC
        
    ADD OBJECT grdMissingValues AS grid WITH ;
        Left = 330, ;
        Top = 72, ;
        Width = 312, ;
        Height = ThisForm.Height - 84, ;
        TabIndex = 5, ;
        DeleteMark = .F., ;
        RecordMark = .F., ;
        Anchor = 15, ;
        ReadOnly =.T., ;
        Visible = .F.
 
    ADD OBJECT lblOData as label WITH ;
        Left = 12, Top = 42, Caption = "OData", FontSize = 12, FontBold = .T.
    
    ADD OBJECT lblResults as label WITH ;
        Left = 330, Top = 42, Caption = "Results", FontSize = 12, FontBold = .T.
    
    ADD OBJECT lblLowerValue as Label WITH ;
        Left = 12, Top = 9, Caption = "Lower value :", AutoSize = .T.

    ADD OBJECT txtLowerValue as TextBox WITH ;
        Left = 96, Top = 6, Width = 48, TabIndex = 1, Value = 1, InputMask = "999"

        PROCEDURE txtLowerValue.Valid()
            IF This.Value < 1
                = MESSAGEBOX("Value must be higher than 0", 16, "Lower value", 3000)
                RETURN 0
            ENDIF
        ENDPROC
        
    ADD OBJECT lblUpperValue as Label WITH ;
        Left = 150, Top = 9, Caption = "Upper value :", Autosize = .T.

    ADD OBJECT txtUpperValue as TextBox WITH ;
        Left = 228, Top = 6, Width = 48, TabIndex = 2, Value = 20, InputMask = "9999"
        
        PROCEDURE txtUpperValue.Valid()
            LOCAL liCheckValue
            
            liCheckValue = ThisForm.txtLowerValue.Value
            
            IF This.Value <= liCheckValue
                = MESSAGEBOX("Upper value must be higher than lower value", 16, "Upper value", 3000)
                RETURN 0
            ENDIF
        ENDPROC

    ADD OBJECT cmdDoit AS CommandButton WITH ;
        Left = 330, Top = 6, Height = 24, TabIndex = 3, Autosize = .T., BackColor = RGB(0, 240, 240), Caption = "The good, the missing and the dupes"

        PROCEDURE cmdDoit.Click()
            LOCAL liUpperValue, liLowerValue
            
            liUpperValue = ThisForm.txtUpperValue.Value
            liLowerValue = ThisForm.txtLowerValue.Value
            
            ThisForm.Caption = "The good, the missing and the dupes between " + ALLTRIM(STR(liLowerValue)) + " and " + ALLTRIM(STR(liUpperValue))

            CREATE cursor csrAllValues (iValue I)

            FOR i = 0 TO liUpperValue - liLowerValue
                INSERT INTO csrAllValues VALUES (liLowerValue + i)

            ENDFOR

            SELECT csrValues.iValue, csrValues.cName, csrDupes.lDupes, .F. as lMissing ;
                FROM csrValues ;
                    JOIN (SELECT iValue, .T. as lDupes FROM csrValues GROUP BY 1 HAVING COUNT(iValue) >= 2) csrDupes ;
                    ON csrValues.iValue = csrDupes.iValue;
                WHERE csrValues.iValue BETWEEN liLowerValue AND liUpperValue ;
                UNION ALL ;
                    SELECT csrValues.iValue, csrValues.cName, csrDupes.lDupes, .F. as lMissing ;
                        FROM csrValues ;
                            JOIN (SELECT iValue, .F. as lDupes FROM csrValues GROUP BY 1 HAVING COUNT(iValue) < 2) csrDupes ;
                            ON csrValues.iValue = csrDupes.iValue ;
                WHERE csrValues.iValue BETWEEN liLowerValue AND liUpperValue ;
                UNION ALL ;
                    SELECT csrAllValues.iValue, "*-" as cName, .F. as lDupes, .T. as lMissing ;
                        FROM csrAllValues ;
                            WHERE iValue NOT in (Select iValue FROM csrValues) ;
                ORDER BY 1 ;
                INTO CURSOR csrGoodDupesMissing

            WITH ThisForm.grdMissingValues
                .Visible = .T.
                .ColumnCount = -1
                .RecordSource = "csrGoodDupesMissing"
        
                 .Column1.Header1.Caption = "ID"
                .Column2.Header1.Caption = "Description"

                .Column3.Header1.Caption = "Dupes"
                .Column3.Width = 54
                .Column3.NewObject("chkDupes","CheckBox")
                .Column3.CurrentControl = "chkDupes"
                .Column3.chkDupes.Caption = ""
                .Column3.chkDupes.Visible = .T.
                .Column3.Sparse = .F.
                .Column3.Alignment = 2
                
                .Column4.Header1.Caption = "Missing"
                .Column4.Width = 54
                .Column4.NewObject("chkMissing","CheckBox")
                .Column4.CurrentControl = "chkMissing"
                .Column4.chkMissing.Caption = ""
                .Column4.chkMissing.Visible = .T.
                .Column4.Sparse = .F.
                .Column4.Alignment = 2

                .SetAll("DynamicFontItalic", "csrGoodDupesMissing.lDupes", "Column")     
                .SetAll("DynamicFontBold", "NOT(csrGoodDupesMissing.lDupes or csrGoodDupesMissing.lMissing)", "Column")     
                .SetAll("DynamicBackColor", "ICASE(csrGoodDupesMissing.lDupes, RGB(225, 225, 0), csrGoodDupesMissing.lMissing, RGB(255, 255, 255), RGB(0, 240, 240))", "Column")     
                
            ENDWITH             
        ENDPROC
        
PROCEDURE Destroy
    Thisform.Release()
    CLOSE ALL
    Clear Events
ENDPROC

PROCEDURE Load
    LOCAL lnI
    
    CREATE CURSOR csrValues (iValue I DEFAULT 1 + INT(RAND() * 29989), cName C(15) DEFAULT "Z" + SYS(2015))
  
    For lnI = 1 To 10000
       Append Blank
    EndFor
    
    INDEX on iValue TAG xiValue
    
    SET ORDER TO xiValue
    
    LOCATE
    
ENDPROC

ENDDEFINE
*********************************************

Enjoy

MarK

 

[Chriss Miller]

It seems though that it is not so much active for VFP posts anymore. I may be wrong on this

That's true, there's a lot of articles about any topic, though.

 

[mmerlinn]

mmerlin,

As you say yourself: "Quick and dirty so likely can be improved upon."
So I won't address that. Just one thing: In FoxPro even up to version 8 an array can at max be 64k items.

But could you point out what doesn't work with RELATION in "plain" FoxPro regarding my RELATION solution? I'm just curious what was introduced later, only.
Is it USE in conjunction with ORDER? If so, you can do that in two lines instead.
Is it SET NEAR? You could also do a second pass with Invnum-1. as said.
Is it the INSERT INTO SQL? I'm quite sure that will require APPEND BLANK followed by REPLACE, but that isn't a big hurdle.

Two nit picking things pop out. Did not dig further.

I (Integer) is not a field option
Dot notation

As for the array length, I was unaware of that since it has never bitten me, but the solution is simple, do a smaller chunk at a time. In this case an array is not likely to ever come close to the max since the array length is only as long as the range being tested.

 

[mmerlinn]

I expanded and turned this into the function NUMGAPS() that I can now use myself, although I did not address the array length issue nor did I address decimal and scientific notation issues.

**********
*
*    Get list of good, missing, or duplicate numbers within any range
*
*        = NUMGAPS(qTableName, qFieldName, qMinValue, qMaxValue[, qOutType])
*
*        qTableName    C:        Table to search
*        qFieldName    C:        Field name in search table
*        qMinValue     N:        Minimum search value
*        qMaxValue     N:        Maximum search value
*        qOutType      C:        Output type
*                                D = Duplicates, G = Good, & M = Missing in any combination
*                                OPTIONAL, Defaults to 'DGM'
*
*        RETURNs with results in cursor named "GAPS"
*
**********

PARAMETERS qTableName, qFieldName, qMinValue, qMaxValue, qOutType
PRIVATE ALL LIKE z*

IF TYPE('qOutType') = 'C' AND NOT EMPTY(CHRTRAN(qOutType, CHRTRAN(qOutType, 'DGMdgm', ''), ''))
    zouttype = UPPER(qOutType)
ELSE
    zouttype = 'DGM'
ENDIF

zCount = qMaxValue - qMinValue + 1
zOffset = qMinValue - 1
zFldWidth = LEN(ALLTRIM(STR(qMaxValue)))

CREATE CURSOR gaps (good N(zFldWidth), missing N(zFldWidth), dups N(zFldWidth))

DIMENSION Array(zCount)
Array = 0

SELECT (qTableName)
SCAN FOR BETWEEN(&qFieldName, zOffset, qMaxValue + 1)
    Array(invnum - zOffset) = Array(invnum - zOffset) + 1
ENDSCAN

FOR z = 1 TO zCount
    DO CASE
    CASE EMPTY(Array(z)) AND 'M' $ zouttype
        INSERT INTO gaps (missing) VALUES (z + zOffset)
    CASE Array(z) > 1 AND 'D' $ zouttype
        INSERT INTO gaps (dups) VALUES (z + zOffset)
    CASE Array(z) = 1 AND 'G' $ zouttype
        INSERT INTO gaps (good) VALUES (z + zOffset)
    ENDCASE
ENDFOR

RETURN

 

[Chriss Miller]

Too bad you don't answer any questions. Well, so be it.

 

[mmerlinn]

Too bad you don't answer any questions. Well, so be it.

If you are talking to me, I DID answer some of your questions, but only those that I could answer. Useless trying to answer questions that I have not looked into. And no point in digging deeper than I needed to dig to determine that your code would not fly in plain Jane Foxpro.

 

[mjcmkrsr]

Hi mmerlinn,

I tested your code.

Unfortunately I found one little flaw: the results show that there are dupes, but do NOT show how many times the same value is present in the table

hth

MarK

 

[mmerlinn]

Hi mmerlinn,

I tested your code.

Unfortunately I found one little flaw: the results show that there are dupes, but do NOT show how many times the same value is present in the table

hth

MarK

Was not designed to give total dups, but ez to fix by changing two lines of code. Will post "fixed" code shortly.

 

[mmerlinn]

OK, Mark, this code fixes the deficiency you noted. I also swatted two bugs that I found.

**********
*
*    Get list of good, missing, or duplicate numbers within any range
*        Lists number of duplicates found in test range, but not locations.
*
*        = NUMGAPS(qTableName, qFieldName, qMinValue, qMaxValue[, qOutType])
*
*        qTableName    C:        Table to search
*        qFieldName    C:        Field name in search table
*        qMinValue     N:        Minimum search value (Integer)
*        qMaxValue     N:        Maximum search value (Integer)
*        qOutType      C:        Output type
*                                D = Duplicates, G = Good, & M = Missing in any combination
*                                OPTIONAL, Defaults to 'DGM'
*
*        RETURNs with results in cursor named "Gaps"
*
*    NOTE
*        This function assumes that the aArray is shorter than the maximum allowable array length.
*        If one needs to test beyond allowable array length, then either call this function multiple
*            times, or modify the function.
*        This function expects whole numbers (Integers), positive or negative.
*            Will not work with decimals nor with scientific notation.
*
**********

PARAMETERS qTableName, qFieldName, qMinValue, qMaxValue, qOutType
PRIVATE ALL LIKE z*

IF TYPE('qOutType') = 'C' AND NOT EMPTY(CHRTRAN(qOutType, CHRTRAN(qOutType, 'DGMdgm', ''), ''))
    zOutType = UPPER(qOutType)
ELSE
    zOutType = 'DGM'
ENDIF

zCount = qMaxValue - qMinValue + 1
zOffset = qMinValue - 1
zFldWidth = LEN(ALLTRIM(STR(MAX(ABS(qMaxValue), ABS(qMinValue))))) + 1

CREATE CURSOR Gaps (Good N(zFldWidth), Missing N(zFldWidth), Dups N(zFldWidth), DupCount N(10))

DIMENSION aArray(zCount)
aArray = 0

SELECT (qTableName)
SCAN FOR BETWEEN(&qFieldName, zOffset, qMaxValue + 1)
    zRow = &qFieldName - zOffset
    aArray(zRow) = aArray(zRow) + 1
ENDSCAN

FOR z = 1 TO zCount
    DO CASE
    CASE 'M' $ zOutType AND EMPTY(aArray(z))
        INSERT INTO Gaps (Missing) VALUES (z + zOffset)   
    CASE 'D' $ zOutType AND aArray(z) > 1
        INSERT INTO Gaps (Dups, DupCount) VALUES (z + zOffset, aArray(z))
    CASE 'G' $ zOutType AND aArray(z) = 1
        INSERT INTO Gaps (Good) VALUES (z + zOffset)
    ENDCASE
ENDFOR

RETURN

 

[mjcmkrsr]

Hi,

Yes, now it works - even in VFP9

MarK

 

[Chriss Miller]

I DID answer some of your questions,

Oh, I thought you were accusing me of nitpicking your code, while I was having questions about almost all lines of my code in conjunction with legacy FoxPro, blame me. You don't want to dig into it, okay. It's just a pitty your code is so brute force, that it hurts. I'm used far better code from your FAQs. Please take the compliment out of that rather than the criticism. If you're fine with your own function now, you are. As simple as that.

 

[mjcmkrsr]

Hi Chriss,

I tested your code. Unfortunately it has one flaw and one misspelling

Insert Into Gaps Values (0,0) && optional for catching the gap of Invoices from 1 to Min(Invoice)-1, when Min(Invoice)>1

If you insert this line you'll have 0 as missing value - which does not make sense

=Seek(Missing.iGapStart,"Invoices","Invoice")

Should read = Seek(Gaps.iGapStart, ...)

Below your code with my suggestions

Create Cursor Gaps (iGapStart I, iGapEnd I)

*!*    Insert Into Gaps Values (0,0) && optional for catching the gap of Invoices from 1 to Min(Invoice)-1, when Min(Invoice)>1

CREATE TABLE invoices (invoice I)

    For lnI = 1 To 100
       INSERT INTO invoices VALUES (1 + INT(RAND() * 199))
    EndFor
    
    INDEX on invoice TAG invoice
    
    SET ORDER TO invoice
    
    LOCATE
    
IF invoice > 1
    Insert Into Gaps Values (1,1)
ENDIF

BROWSE NOWAIT

*!*    Use Invoices In 0 Order Invoice

Use Invoices In 0 Again Alias CheckNext Order Invoice
Select CheckNext
Set Relation To Invoice + 1 into Invoices
Scan For Eof("Invoices")
   Insert into Gaps (iGapStart) Values (CheckNext.Invoice + 1)
EndScan

Set Near On
Select Gaps
Scan
   =Seek(Gaps.iGapStart,"Invoices","Invoice")
   If !Eof("Invoices")
      Replace iGapEnd With Invoices.Invoice - 1
   Else
      Delete In Gaps
   Endif
EndScan
Set Near Off
Go Top
BROWSE

CLOSE ALL
CLEAR ALL
RETURN

MarK

 

[Chriss Miller]

MarK,

If you insert this line you'll have 0 as missing value - which does not make sense

I should have added how to interpret this and why this makes sense: If you have a missing 1 in your data, or any number of low values up to N, then this (0,0) will end up being 0,N-1 and whether you take 0 as non used or used number, you'll now the ap at the start of data with that. If you instead make it (1,1) that won't work anymore. It would always make 1 a gap and that's not the case, normally.
Edit: To be precise you'd end up with a record (1,0) that makes no sense. So actually I put very much thought into making it 0,0 and interpreting it right gives you a gap at the start, too.

Should read = Seek(Gaps.iGapStart, ...)

Yes, that's an oversight of me, as I initially named my Gaps cursor "Missing" and didn't fix that here.

Here's my code with initializing sample data, you can test it with different scnearios including your "mending" of to (1,1)

Close Databases All
Create Cursor YourTable (iValue I)
Index on iValue Tag iValue

Dimension laValues(ALines(laValues,"1,2,3,4,5,8,9,11,12,13,15",0,","),1)
Select YourTable
Append From Array laValues

Create Cursor Gaps (iGapStart I, iGapEnd I)
Insert Into Gaps values (0,0)

Select 0
Use Dbf("YourTable") Again Alias CheckNext Order iValue
Set Relation To iValue+1 into YourTable
Scan For Eof("YourTable")
   Insert into Gaps (iGapStart) Values (CheckNext.iValue+1)
EndScan

Set Near On
Select Gaps
Scan
   =Seek(Gaps.iGapStart,"Yourtable","iValue")
   If !Eof("Yourtable")
      Replace iGapEnd With Yourtable.iValue-1
   Else
      Delete
   Endif
EndScan
Locate && does the same as Go Top
If iGapEnd>0
   Replace iGapStart With 1 && optional, but if you don't want a mention of 0, okay.
Else
   Delete && delete first record, if unnecessary.
Endif
Browse

Including a postprocessing of the 0,0 record to 1,N-1, if there is a gap at the start of the data or deleting it, if data starts with 1. (Then the record stays 0,0)

Well, and to restate what the comment already says, if you only care for gaps in data between its min and max values you can just not insert this 0,0 record and make a 1 to max gap finder a min to max gap finder. That's a matter of taste.

 

[mjcmkrsr]

Hi Chriss,

Thanks. Works now. Best with SET DELETED ON - otherwise you see the records marked for deletion

MarK

 

[Chriss Miller]

Of course, but even if you see them, you see them with the deletion mark, that's not rendering it wrong. SET DELETED ON is a standard setting in my IDE, unless I explicitly need to see deleted records, and it's recommended in an EXE, unless you don't want to be able to delete records so the become actually supressed, as if they really were removed. It's bad practice, indeed an errror, if you pack your application code with PACKs, which need exclusive access you normally will not have in a muti user application, taken aside how badly it peforms on large tables, as it essentially has to recreate a dbf without deleted records. In that regard the better practice is record recycling, which requires SET DELETED OFF at least during that recycling process of finding a deleted record in a dbf to recycle with RECALL and then BLANK DEFAULT AUTOINC (VFP9).

Likewise, by the way, you will have SET NEAR OFF, because you want your SEEKs to find matches, not near matches, unless you explicitly need that, as my second pass SCAN loop. So I actually don't bother to store the inital value of the NEAR setting to restore it, here. You can opt for that, too. Tha major work in this is to provide a fast gap finding. Anything is fast with a small set of data, but not everything scales well.

 

[mjcmkrsr]

Hi Chriss,

I personally also like SQL, I just don't trust the VFP SQL engine to optimally do the NOT IN (SELECT field FROM table) construct, in this special case.

Could you please explain.

TIA

MarK

 

[Chriss Miller]

Well, the expression field NOT IN (query) actually means finding a set not in another set, just because the first field is a single field, it's not a single value, it's all the fields values in a subquery, so it's a set comparison. The best thing you can do is seeking all values in the other set. That's straight forward what my code does. The only thing you can do to optimize this further is knowing whether it's better to look for all values from the left to be in the right set or vice versa. VFP doesn't have what server databases have for that matter: a) statistics on data maintained as meta data. Edit: And b) local processing on the server side.

Simply because VFP is no server doing such a thing. In this case it wouldn't matter much as both sets are mainly the same single table we investigate, so statistics on the data would only tell you it doesn't matter which set you look for in the other set of omitted values (i.e. gaps).

Well, so the best thing VFP query optimization could do is what I just do straight away directly. So I just spare analysis time for that.

 

[mjcmkrsr]

I understand, but I nowhere found this kind "mistrust" - neither in the "Hacker's Guide"

We're actually far more likely to use NOT IN to find records that don't have a match in a sub-query. This is the best way to find the differences between two tables—all the records contained in one, but not in the other.

nor in Tamar's many papers on SQL.

May be she could chime in for further input.

TIA

MarK