How to find the Max difference between two tests by counting number of cells between events.

[#1]

I have attached a mini file.

If the cell of a lot passes TEST 1 and TEST 2 its status is PASS.

If a cell fails TEST 1 and TEST 2 its status is SAME.

If a cell fails a test but passes the other test its status is DIFF.

For each lot I am trying to determine the Max distance by cell count from a DIFF to its nearest SAME.

What would be the best way to go about this?

Thanks

Fiona

[#2 · Accepted solution]

Here is a little script that calculates the new columns you want

Names Default To Here( 1 );
dt = Current Data Table();
dt << New Column( "Calculated Status",
	character,
	set each value(
		If(
			:Test 1 || :Test 2 == "PP", "PASS",
			:Test 1 || :Test 2 == "FF", "SAME",
			"DIFF"
		)
	)
);


If( Try( dt:cellNum << get name, "" ) == "",
	dt << New Column( "cellNum" )
);
dt:cellNum << set each value( Num( :CELL ) );

dt << New Column( "Calculated Distance",
	set each value(
		If( :Calculated Status == "DIFF",
			currLot = :LOT;
			cells = dt:cellNum[dt <<
			get rows where(
				:LOT == currLot & :Calculated Status == "SAME"
			)];
			dif = Min( Abs( Num( :CELL ) - cells ) );
		,
			dif = .
		)
	)
);

dt << delete columns( "cellNum" );

[#3]

Thanks Jim, this is perfect.

Can you explain what is happening at part of script below?

If( Try( dt:cellNum << get name, "" ) == "",
	dt << New Column( "cellNum" )
);
dt:cellNum << set each value( Num( :CELL ) );

[#4]

First, it checks to see if there is a column called "CellNum" and if not, create it.

Second, set the values for the column.