Previous and next non-missing values in a column

PathReindeer523

I’m fairly new to JMP, and I’ve come across a problem with which I’m really struggling. I use JMP Pro 17. I have a data sheet with one column with distance from a starting point (“Distance”) and one column with a signal intensity value at that distance (“Signal”). I’ve found signal local maxima (peaks) by making a formula column which yields a 1 if the signal value at that distance is the largest value within a given range (Signal[row] == max(Signal[row-n, row+n]), else missing. My problem, however, is the following; I wish to make another formula column which gives the distance at each peak to the closest peak.

 

In other words, I wish to, if “Peak?” == 1, find the distance value at the previous and the next rows where “Peak?” == 1, find the absolute value of the difference between the distance at the current row and the distance at those rows, and yield the minimum of those two values.

 

So far, I’ve made a “helper column” which if the row contains a local signal maximum gives the distance at that value. In other words, I have a column with the distance values at peak signal values, and missing values in all the rows which aren’t peaks. However, I cannot find a way to define by formula “previous non-missing value” or “next non-missing value”. I would be extremely grateful for any advice, either on how to make a formula for this, or if there are better ways to solve the actual problem of finding minimum distance between peaks. 

jthi

First idea that came to my mind so it does look fairly complicated

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(12),
	Compress File When Saved(1),
	New Column("Peaks", Numeric, "Continuous", 
		Set Values([., 1, ., ., 2.5, ., ., ., ., 3.1, ., ., 2])
	)
);


dt << New Column("DistanceToPeak", Numeric, Continuous, Formula(
	As Constant(m = :Peaks << get values;
		peak_rows = Loc(m);
		peak_values = m[peak_rows];
	);
	
	If(!IsMissing(:Peaks),
		peak_distances = peak_rows - Row();
		
		previous_peak = Loc(peak_distances < 0);
		If(N Items(previous_peak) == 0,
			previous_distance = .;
		,
			previous_distance = Abs(m[Row()] - peak_values[Reverse(previous_peak)[1]]);
		);
		
		next_peak = Loc(peak_distances > 0);
		If(N Items(next_peak) == 0,
			next_distance = .;
		,
			next_distance = Abs(m[Row()] - peak_values[next_peak[1]]);
		);
		Min(previous_distance, next_distance);
	,
		.
	);
));