Page 1 of 1
Find value in data columns
Posted: Mon Dec 23, 2019 8:22 pm
by geo
Hello
Is there a way to find the value closest to 2755 in column 1 and return the value from column 2?
Kind of like the match/index functions in excel
Thanks for any help
2780.5273 7010.989
2765.9229 7494.5054
2748.073 7978.022
2731.846 8498.732
2717.2415 8963.651
2699.3916 9484.361
2679.919 9967.878
2627.992 10507.186
2576.065 10972.1045
2529.006 11474.218
2475.4563 11976.331
Re: Find value in data columns
Posted: Mon Dec 23, 2019 9:15 pm
by richmond62
Are those numbers in:
1. A space/tab delimited text document -> scrolling list field.
2. A table field,
3. A data grid?
Re: Find value in data columns
Posted: Mon Dec 23, 2019 9:19 pm
by geo
the numbers can be in any of the three, whatever works best
Re: Find value in data columns
Posted: Mon Dec 23, 2019 9:39 pm
by richmond62
I put your
data into a
scrolling list field and did what was, frankly, some baby Maths:
-
-
Button "DIFF from 2755":
Code: Select all
on mouseUp
set the itemdelimiter to tab
put 1 into KOUNT
repeat until line KOUNT of fld "rawDATA" is empty
put item 1 of line KOUNT of fld "rawDATA" into XXX
put (2755 - XXX) into ZZZ
if ZZZ < 0 then
put (ZZZ * (-1)) into ZZZ
end if
put (line KOUNT of fld "rawDATA") & tab & ZZZ into line KOUNT of fld "cooked"
add 1 to KOUNT
end repeat
end mouseUp
Button "CLOSEST to 2755":
Code: Select all
on mouseUP
set the itemDelimiter to tab
sort lines of fld "cooked" by item 3 of each
put item 2 of line 1 of fld "cooked" into fld "fRESULT"
end mouseUP
Re: Find value in data columns
Posted: Mon Dec 23, 2019 11:37 pm
by dunbarx
Hi.
Two ways I would do it. Step through each and see which one resonates with you. Given your data in a field 1:
Code: Select all
on mouseUp
get fld 1
repeat with y = 1 to the number of lines of it
put abs(word 1 of line y of it - 2755) & "," && word 2 of line y of it & return after temp
end repeat
sort temp numeric by item 1 of each
answer line 1 of temp
end mouseUp
on mouseUp
get fld 1
repeat for each line tLine in it
put abs(word 1 of tLine - 2755) into closestvalue[word 2 of tLine]
end repeat
combine closestvalue with return and space
sort closestvalue numeric by word 2 of each
answer line 1 of closestvalue
end mouseUp
A word of advice. Put a delimiter that is easily identified to delimit your data. It took me a bit to determine that you were using spaces. Commas or tabs are much better. I added commas in the first example.
Craig
Re: Find value in data columns
Posted: Mon Dec 23, 2019 11:57 pm
by [-hh]
You could use a sort function (shortens Craig's first handler a little bit):
Given your data is in field "IN".
Code: Select all
on mouseUp
answer closestValue(2755, fld "IN")
end mouseUp
function closestValue pVal, pStr
sort pStr numeric by abs(pVal - word 1 of each)
return word 2 of line 1 of pStr
end closestValue
Re: Find value in data columns
Posted: Tue Dec 24, 2019 4:53 am
by dunbarx
Hermann.
Very sleek.
Craig
Re: Find value in data columns
Posted: Tue Dec 24, 2019 8:23 pm
by geo
Thanks guys, that put me on the right track