ComparativeValuePosition ( VList ; Comparator ; Value )

Quickly search for a comparative (>, <) number in an ascending number value list. Returns the value closest to the boundary that meets the criterea

Be the first to rate this function Log in to vote

Bill Thurmes - Show more from this author
MDCA
http://www.miyotadca.com

Sample input:

ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">=" ; 5.27 )
ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; "<=" ; 75 )
ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">" ; 75 )

Sample output:

2
8
0

Function definition: (Copy & paste into FileMaker's Edit Custom Function window)

/*************************************************************
# ComparativeValuePosition( VList ; Comparator ; Value )
# Quickly search for a number in a ascending-sorted list of number values, to get the position of the value in VList
# For instance, looking in VList for the first value >= 79
# Result is the position of, for >, the first value larger than the Value; for <, the last value less than the Value
# So, it finds the values just above or just below (or, for =, at) the Value boundary
# This is most useful when VList has >100 values, particularly when they're roughly linear
# This assumes that the values ARE linear, and computes where the boundary should be, and jumps to that position
# If it hits it, it gives that as an answer; otherwise it throws out the extra values from the side of the boundary it is on, and tries again
# The 'throwing out' is what makes this work quickly, and this alternates throwing out early or late values
# 
# Parameters
# VList must be an ascending sorted ¶-delimited list of numbers. It must have no empty values and no leading or trailing ¶s, 
# except for the one trailing ¶ often used in FM value lists
# Value is the target number
# Comparator can be >, >=, ≥, <, <=, ≤
# If VList contains no values that meet the criteria (all outside the range), the result is 0
#  
# Example: ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">=" ; 5.27 ) => Result 2 (because 6, in position 2, is the first value higher than 5.27)
#		ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; "<=" ; 75 ) => Result 8 (because the highest number less than 75 is 12, position 8)
# 		ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">" ; 75 ) => Result 0 (because no numbers in list are > 75)
# 
# Comparisons: for a 3648-item value list, choosing two values that were inexact matches and evaluated as items 791 and 2915,
# ValuesLessThanOrEqual gave wrong answers, took 2600ms; ValuePlaceInSList took 1611 ms; this CF took 13 ms
#
# Idea comes from ValuePlaceInSList (Steven Zeidel), which steps through one by one; 
# I wanted a faster method for large value lists that were roughly linear, such as wavelengths from a spectrometer
# Written 5/24/2020 by Bill Thurmes
*************************************************************/
Let ( [
	/*Simplify comparator*/
	~cp = Case ( Comparator = "≤" ; "<=" ; Comparator = "≥" ; ">=" ; Comparator ) ;
	~vc = ValueCount ( VList ) ;
	
	/*check to see whether, if ~cp contains =, the value itself is in the list, and also whether all list items are outside the range*/
	~eq = Case ( PatternCount ( ~cp ; "=" ) ; 
					If ( PatternCount ( ¶ & VList & ¶ ; ¶ & Value & ¶ ) ; 
						PatternCount ( Left ( ¶ & VList & ¶ ; Position ( ¶ & VList & ¶ ; ¶ & Value & ¶ ; 1 ; 1 ) ) ; ¶ ) ;
						"" )
				) ;
	~outOfRange = Case (
				GetAsNumber ( GetValue ( VList ; 1 ) ) > GetAsNumber ( Value ) ;
						Case ( PatternCount ( ~cp ; ">" ) ; 1 ; 0 ) ;
				GetAsNumber ( GetValue ( VList ; ~vc ) ) < GetAsNumber ( Value ) ;
						Case ( PatternCount ( ~cp ; "<" ) ; ~vc ; 0 ) ;
				GetAsNumber ( GetValue ( VList ; 1 ) ) = GetAsNumber ( Value ) ;
						Case ( PatternCount ( ~cp ; "=" ) ; 1 ; 0 ) ;
				GetAsNumber ( GetValue ( VList ; ~vc ) ) = GetAsNumber ( Value ) ;
						Case ( PatternCount ( ~cp ; "=" ) ; ~vc ; 0 ) ) ;
						
	/*if Value = a value in the list, or if the comparison is out of range, write ~result; return the result as an answer*/
	~result = Case ( not IsEmpty ( ~eq ) ; ~eq ; Not IsEmpty (~OutOfRange) ; ~OutOfRange ; "" ) ;
	
	/*Now search for the Value boundary using a While loop*/
	~WhileResult = While ( [ 
					`result = ~result ; //if ~result already filled, don't do any While loops
					`VList = VList ;
					`Value = Value ;
					`cpl = Left ( ~cp ; 1 ) = "<" ;
					`cpg = Left ( ~cp ; 1 ) = ">" ;
					`eq = PatternCount ( ~cp ; "=" ) ;
					`rising = 1 ; //used as a flag to choose the equation used
					`vc = 0 ; //value count
					`firstVal = "" ; //1st, 2nd are values chosen to look for a boundary between
					`secondVal = "" ;
					`dPerVal = "" ; //average separation between values
					`boundary = 0 ; //flag: did you find the boundary?
					`NoToDrop = 0 ; //cumulative number of values dropped from the front of the value list (LeftValues)
					`rangeToCut = "" ] ;
					
			IsEmpty ( `result ) ;
					
					[ `vc = ValueCount (`VList) ;
					`dPerVal = ( GetValue ( `VList ; `vc ) - GetValue ( `VList ; 1 ) ) / (`vc - 1) ;
					
					/*alternate between estimating the proper position from the beginning or end of list*/
					/*The Max & Min parts are because Value#s have to be between 1 and the # of values*/
					`firstVal = If ( `rising ; 
						/*estimating from the beginning or end gives different results, and lops unneeded values from beginning or end of list*/
						Min ( Max ( Int ( ( `Value - GetValue (`VList ; 1 ) ) / `dPerVal ) - 1 ; 1 ) ; `vc - 1 ); 
						Min ( Max ( Int ( `vc - ( GetValue (`VList ; `vc ) - `Value ) / `dPerVal ) ; 1 ) ; `vc - 1 ) ) ;
					`secondVal = `firstVal + 1 ;
					
					`boundary = If ( GetAsNumber (`Value) > GetAsNumber(GetValue(`VList ; `firstVal )) and GetAsNumber (`Value) < GetAsNumber(GetValue(`VList ; `secondVal )) ; 1 ; 0 ) ;
					
					/*if this is at a boundary, issue result; else cut `VList and, if you cut items at the beginning, add to `NoToDrop*/
					`result = Case ( not `boundary ; "" ; 
								`cpg ; `secondVal +`NoToDrop ;
								`cpl ; `firstVal + `NoToDrop ) ;
					`rangeToCut = Case ( not IsEmpty ( `result ) ; "" ; 
								GetAsNumber ( GetValue ( `VList ; `firstVal ) ) > GetAsNumber ( `Value ) ; `secondVal & ¶ & `vc ; 
								1 & ¶ & `firstVal 
								) ;
					`NoToDrop = Case ( GetValue ( `rangeToCut ; 1 ) <> 1 ; `NoToDrop ;
								GetValue ( `rangeToCut ; 1 ) = 1 ; GetValue ( `rangeToCut ; 2 ) - GetValue ( `rangeToCut ; 1 ) + 1 + `NoToDrop ) ;
					`VList = Case ( GetValue ( `rangeToCut ; 1 ) = 1 ; 
								RightValues ( `VList ; `vc - `firstVal );
								LeftValues ( `VList ; `secondVal ) 
								) ;
								
					/*`rising is used as a flag to determine which equation to use*/
					`rising = Abs(`rising - 1) ] ;
					
					/*result of While loop*/
			`result 
		) //end While
		] ;
Case ( not IsEmpty ( ~result ) ; ~result ; ~WhileResult ) 
)

# Quickly search for a number in a ascending-sorted list of number values, to get the position of the value in VList
# For instance, looking in VList for the first value >= 79
# Result is the position of, for >, the first value larger than the Value; for <, the last value less than the Value
# So, it finds the values just above or just below (or, for =, at) the Value boundary
# This is most useful when VList has >100 values, particularly when they're roughly linear
# This assumes that the values ARE linear, and computes where the boundary should be, and jumps to that position
# If it hits it, it gives that as an answer; otherwise it throws out the extra values from the side of the boundary it is on, and tries again
# The 'throwing out' is what makes this work quickly, and this alternates throwing out early or late values
#
# Parameters
# VList must be an ascending sorted ¶-delimited list of numbers. It must have no empty values and no leading or trailing ¶s,
# except for the one trailing ¶ often used in FM value lists
# Value is the target number
# Comparator can be >, >=, ≥, <, <=, ≤
# If VList contains no values that meet the criteria (all outside the range), the result is 0
#
# Example: ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">=" ; 5.27 ) => Result 2 (because 6, in position 2, is the first value higher than 5.27)
# ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; "<=" ; 75 ) => Result 8 (because the highest number less than 75 is 12, position 8)
# ComparativeValuePosition ( "5¶6¶7¶8¶9¶10¶11¶12¶" ; ">" ; 75 ) => Result 0 (because no numbers in list are > 75)
#
# Comparisons: for a 3648-item value list, choosing two values that were inexact matches and evaluated as items 791 and 2915,
# ValuesLessThanOrEqual gave wrong answers, took 2600ms; ValuePlaceInSList took 1611 ms; this CF took 13 ms
#
# Idea comes from ValuePlaceInSList (Steven Zeidel), which steps through one by one;
# I wanted a faster method for large value lists that were roughly linear, such as wavelengths from a spectrometer

Comments

Note: these functions are not guaranteed or supported by BrianDunning.com. Please contact the individual developer with any questions or problems.

ComparativeValuePosition ( VList ; Comparator ; Value )

Comments

Support this website.

Support this website.