Nearest Neighbor Number search

[crathbun]

Hello Bubblers! Happy almost Christmas!

I am looking to a nearest neighbor search on a set of records. Here is a sample record set

#,Color
1, blue
2 ,green
4,yellow
7,purple
12,red

If I search for the number 5, I’d like to return “4, yellow”.
If I search for the number 7, I’d like to return "7,purple’
If I search for the number 11, I’d like to return “12,red”

In the case of a tie (if I search for the number 3, it would be equidistant from 2 and 4) in terms of distance to the next number, I’d be happy with just setting the higher or lower number as a setting.

To note, this is a gross over simplification of my dataset of course. Unfortunately rounding operators can’t really cut it.

Appreciate any thoughts out there!

Cheers

[mikeloc]

Hi there, @crathbun… maybe you have already thought of what I am about to suggest and your question is more along the lines of how to do it in Bubble, but one way to go would be to get the absolute difference between the number for which you are searching and every number in the set of records. Then, get the color from the record with the lowest absolute difference, and you’d be good to go.

I have a working example where (and it wouldn’t necessarily have to be done this way) I am saving the absolute difference between the search value and each record’s number to a field on each record (which makes it easy to get the record you need), and I save the difference (by making changes to a list of things) when the search value is entered into an input. If it would be helpful (even as food for thought), I can provide details from that example.

Best…
Mike

[firstfifteensoftware]

Using your data, this search would result in 4-yellow if I keyed in “5”:

…and this search would result in 7-purple:

You now have the two colors closest to the the # you entered. If both colors are the same, then the color is actually in your data.

But if not…

…then calculate the difference between the lowest found and the number entered:

…then calculate the difference between the highest found and the number entered:

Whichever calculation results in the lowest number is the number closest to the number you entered.

I think.

EDIT: Added text to state what number I keyed in for the search, which is 5.

Also, I’ve been up since 4am so this may not even work at all…but I think it does.

[troy.roberge]

I think if you want more accurate help, a better explanation of the available options and data would help. Otherwise my answer to your OP would be a simple conditional IF, THEN. But that might not be as clear cut for your data.

[crathbun]

Thank you! That got me to where I needed to go! Much appreciated!

[firstfifteensoftware]

Oddly enough, what you were looking for is remarkably close to a lesson I had to do in one of my first computer science classes where we had to insert a new value into a sorted list and set the backward and forward pointers based on its value in relation to the previous and next values.

Sounds simple, until you realized it had to be coded in assembly and on punch cards.

Yeah, I’m THAT old.

But glad I was able to help!

[crathbun]

Haha. Awesome.

thanks for the help!