?
Solved

Finding Closest Match (integer)

Posted on 2013-11-02
6
Medium Priority
?
31 Views
Last Modified: 2016-05-25
I am writing a program that will match two entries based on weight within 2 lbs.  I have a table with the following structure:

entry#    weight
1            135
2            137
3            154
4            190
5            175
6            188
7            193
8            177
9            210
10          215
11          235
12          238
13          240

Each entry can only be matched up against one other entry...no duplicates.  Weight must be within 2 lbs (either above or below) and can not exceed 2 lbs.  All entries left over (no match found) will be listed as "no match".  I am writting this in VB.NET and SQLLite b/c the program will need to be portable. This is a Windows form.

From above
entry 1 matches entry 2
entry 3 No Match
entry 4 matches  entry 6
entry 5 matches entry 8
entry 7 no match
etc...

Where I'm stuck is in the weight matching to 1) find a match within 2 lbs (up or down so maybe that's 4lbs ??) 2) preventing the same entry from matching itself (like on a join).

What's my best options for this task?
0
Comment
Question by:azyet24
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 
LVL 83

Expert Comment

by:CodeCruiser
ID: 39619301
I think one way of achieving this would be to sort the list. This way, you can match each enter up and down until the difference is more than 2.
0
 
LVL 46

Expert Comment

by:Kent Olsen
ID: 39619355
One of the issues that you have is that there's not enough separation between the first two weights.  A weight of 136 matches both entry 1 and 2.
SELECT entry# FROM {tablename}
WHERE weight >= MyWeight -2
  AND weight <= MyWeight + 2;

Open in new window


That should do it, once you resolve the overlap between 1 and 2.  And SQLLITE will handle that just fine...



Good Luck,
Kent
0
 

Author Comment

by:azyet24
ID: 39619364
Kent, so if I sort the list ASC and then set the first value as MyWeight I can see the first iteration to match up with the second record.  So I would then set MyWeight as the 3rd record and proceed unti it finds one.  So I guess I'd need to store records that didn't match in an array so that I'd know the ones that were not a match (there could be hundreds of records in list).  Is that what you had in mind by your MyWeight reference?
0
 

Accepted Solution

by:
azyet24 earned 0 total points
ID: 39619388
I'm making progress but this has gotten a bit more difficult now b/c I need to now introduce teams into the code.  Meaning, I still need to match up weight but team members can't be matched against each other.  

In my code below I am successful with matching all records when ignoring teams, but from here I'm not sure how I'd keep up with multiple entries from same team.  

Here's my code so far:

Dim sql As String = "select id,team,entry,band,weight from entries order by weight asc"

 For Each Dr As DataRow In ds.Tables(0).Rows
                Dim team As String = Dr(1)
                Dim entry As String = Dr(2)
                Dim band As String = Dr(3)
                Dim weight As String = Dr(4)

                If counter > 0 Then ' first record would not have previous to compare so we skip it
                  
                    If (weight - previousweight <= 2) Then
                        'Add previous and current record to dgassignment as a match
                        dgAssignments.Rows.Add(previousentry, previousband, (Int(previousweight / 16)).ToString & "lbs " & (previousweight Mod 16) & "oz", entry, band, (Int(weight / 16)).ToString & "lbs " & (weight Mod 16) & "oz")
                        'clear previous since both prev and current are match
                        previousteam = 0
                        previousentry = 0
                        previousband = 0
                        previousweight = 0
                    Else
                        'Add previous to no match datagrid
                        dgNoMatch.Rows.Add(previousteam, previousentry, previousband, (Int(previousweight / 16)).ToString & "lbs " & (previousweight Mod 16) & "oz")
                        
                         'Add current entry as previous to be used in next loop
                        previousteam = team
                        previousentry = entry
                        previousband = band
                        previousweight = weight
                    End If
                Else
                    previousteam = team
                    previousentry = entry
                    previousband = band
                    previousweight = weight
                End If
                counter = counter + 1
            Next

Open in new window

0

Featured Post

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question