How to efficiently find the first position in a specified range of data that meets the requirements?

[#1]

For example, Big Class.jmp
The calculation steps are:

if(row()>30,contains(:height[Index(1,30)],abs(:height[Index(1,30)]-:height)<3))

If the number of lines is greater than 30,
compare the data with the absolute value difference between the height of lines 1 -- 30 and the height of this line is less than or equal to 2, and calculate the number of lines 1 -- 30 that meet the above conditions first.

Thanks Experts!

[#2]

I tried this stupid method and got results.Ask the experts Can this formula be simplified?

Thanks!

If( Row() > 30,
r1 = Contains( height[Index( 1, 30 )], height + 2 );
r2 = Contains( height[Index( 1, 30 )], height + 1 );
r3 = Contains( height[Index( 1, 30 )], height );
r4 = Contains( height[Index( 1, 30 )], height - 1 );
r5 = Contains( height[Index( 1, 30 )], height - 2 );
Min( If( r1 == 0, 99, r1 ), If( r2 == 0, 99, r2 ), If( r3 == 0, 99, r3 ), If( r4 == 0, 99, r4 ), If( r5 == 0, 99, r5 ) );
)

[#3 · Accepted solution]

Hi @lwx228 ,

I am not sure this is any better than your formula, but here is another way to do the same thing (I think).  Note that this will be slow for larger tables; if you are dealing with many rows I you could create a lookup table with the desired row number corresponding with all possible values of height and then join it to the table.

If( Row() > 30,
	firstrowmatch = 0;
	For( r = 1, r <= 30 & firstrowmatch == 0, r++,
		mtch = Contains( Index( :height - 2, :height + 2, 1 ), :height[r] );
		If( mtch > 0,
			firstrowmatch = r
		);
	);
	firstrowmatch;
)

[#4 · Accepted solution]

If I have understood the question correctly this might work (using Loc and matrix subscripting):

If(Row() > 30,
	m = Loc(Abs((:height << get as matrix)[1::30] - :height) < 3, 1);
	If(N Items(m) > 0,
		m[1],
		.
	)
);

Full example with Big Class:

View more...

Names Default To Here(1);
dt = Open("$SAMPLE_DATA/Big Class.jmp");

dt << New Column("pos", Numeric, Continuous, Formula(
	If(Row() > 30,
		m = Loc(Abs((:height << get as matrix)[1::30] - :height) < 3, 1);
		If(N Items(m) > 0,
			m[1],
			.
		)
	);
));

[#5]

Thanks to the experts for their help. Through the study of these methods, I know more efficient methods.