How to look up data and create new column based on nearest match?

Question

* I have sample data in table sample1 and the look up table efficiency( please find attached both the tables). I want to find the closest match of data column in table sample1 with resplect to IOUT column in look up table efficiency. Based on the nearest match I want to populate efficiency column in table sample1 in accordance with look up table:
* Below is the code I am trying/* Open a sample data table */
dt = Open( "C:\Users\Test\Sample data\Sample1.jmp" );

/* Add a column for the assignment */
dt << New Column( "Efficiency", Numeric );

/* Open a lookup table */
lt = Open( "C:\Users\Test\Sample data\efficiency_0.7.jmp" );
/* Loop through each row of the original data table */
For( i = 1, i <= N Rows( dt ), i++, 
	/* Obtain a matrix contining the row number of the lookup table
	   where the current falls within the range */
	lowRange = lt << Get Rows Where(
		Column( lt, "IOUT" )[] < Column( dt, "data" )[i] & 
		Column( dt, "data" )[i] <= Column( lt, "IOUT" )[]
	);
	/* Extract the row number from the matrix */
	r = lowRange[1];
	/* Assign the efficiency value to the original data table */
	Column( dt, "Efficiency" )[i] = Column( lt, "low" )[r];
);

* I want to find nearest match of IOUT with data and populate the efficiency accordingly

Sample1.jmp

efficiency.jmp

txnelson · Answer

* Assuming the Lookup Table(efficiency) is sorted from low to high on column IOUT
* Assuming the Lookup Table is not real large. The methodology being used is not very efficient.

This code should work well

/* Open a sample data table */
//dt = Open( "C:\Users\Test\Sample data\Sample1.jmp" );
dt = Data Table( "Sample1" );

/* Add a column for the assignment */
dt << New Column( "Efficiency", Numeric );

/* Open a lookup table */
//lt = Open( "C:\Users\Test\Sample data\efficiency_0.7.jmp" );
lt = Data Table( "efficiency" );

/* Loop through each row of the original data table */
For( i = 1, i <= N Rows( dt ), i++, 
	// Find the row with the closest IOUT value below the data value
	theLowRow = Max( lt << get rows where( lt:IOUT <= dt:data[i] ) );
	// Get the IOUT value for theLowRow
	low = lt:IOUT[theLowRow];

// Find the row with the closest IOUT value above the data value
	theHighRow = Min( lt << get rows where( lt:IOUT >= dt:data[i] ) );
	// Get the IOUT value for theLowRow
	high = lt:IOUT[theHighRow];

// Find the closest of these two values and write the Efficiency value
	If( Abs( low - dt:data[i] ) < Abs( high - dt:data[i] ),
		dt:Efficiency[i] = lt:low[theLowRow],
		dt:Efficiency[i] = lt:low[theHighRow]
	);

/*/* Obtain a matrix contining the row number of the lookup table
	   where the current falls within the range */
	lowRange = lt << Get Rows Where(
		Column( lt, "IOUT" )[] < Column( dt, "data" )[i] & 
		Column( dt, "data" )[i] <= Column( lt, "IOUT" )[]
	);
	/* Extract the row number from the matrix */
	r = lowRange[1];
	/* Assign the efficiency value to the original data table */
	Column( dt, "Efficiency" )[i] = Column( lt, "low" )[r];*/
);

Mark_Bailey · Answer

This reply is not a solution but a suggestion about a direction you might take. See the JSL matrix functions for distance metrics. Similarly, you might use a JMP platform that is based on distance for agglomeration, such as clustering or discrimination.