How the pass/fail formula works？

[#1]

I'm having a problem where the formulas are returning null values.
The raw table is the raw data and there is a specification table, which is used to determine whether the raw data passes or fails according to the specification table. I have written a JSL, please help me to see what the problem is, thanks.

Names Default To Here( 1 );
//raw data
dt = Data Table( "raw data001" );

dt1 = dt << subset( all rows, all columns );

//spec table

dtbz = Data Table( "Spec" );
LSL = dtbz[0, 3];
USL = dtbz[0, 4];
dtbz << New Column( "combine", formula( :Project || :Item ) );

// Create separate judgment columns for the columns to be judged
items = Design( dtbz:Item << get values(), <<levels )[2];



// new column to justify the pass/no pass


For( i = 1, i <= N Items( items ), i++,
	it = items[i];
	
	dt1 << New Column( it || "—pass/no pass",
		formula(
					
			If(
				:Project == "A" & N Items( Loc( dtbz:combine << get as matrix(), "A" || it ) ) > 0,
					dtbz:LSL[Loc( dtbz:combine << get as matrix(), "A" || it )[1]] <= Column( dt1, items[i] ) <= dtbz:USL[
					Loc( dtbz:combine << get as matrix(), "A" || it )[1]], 	
					
				:Project == "B" & N Items( Loc( dtbz:combine << get as matrix(), "B" || it ) ) > 0,
					dtbz:LSL[Loc( dtbz:combine << get as matrix(), "B" || it )[1]] <= Column( dt1, items[i] ) <= dtbz:USL[
					Loc( dtbz:combine << get as matrix(), "B" || it )[1]], 	
					
				:Project == "C" & N Items( Loc( dtbz:combine << get as matrix(), "C" || it ) ) > 0,
					dtbz:LSL[Loc( dtbz:combine << get as matrix(), "C" || it )[1]] <= Column( dt1, items[i] ) <= dtbz:USL[
					Loc( dtbz:combine << get as matrix(), "C" || it )[1]], 	
					
				:Project == "D" & N Items( Loc( dtbz:combine << get as matrix(), "D" || it ) ) > 0,
					dtbz:LSL[Loc( dtbz:combine << get as matrix(), "D" || it )[1]] <= Column( dt1, items[i] ) <= dtbz:USL[
					Loc( dtbz:combine << get as matrix(), "D" || it )[1]], 	
					
						
						
			)
		)
	)
);

[#2 · Accepted solution]

Here is a rework of your script that I think simplifies it, and still provides you with your yields

Names Default To Here( 1 );
//raw data
dt = Data Table( "raw data001" );

dt1 = dt << subset( all rows, all columns );

//spec table

dtbz = Data Table( "Spec" );
LSL = dtbz[0, 4];
USL = dtbz[0, 5];
//dtbz << New Column( "combine", formula( :Project || :Item ) );
dtbz << New Column( "combine", character, formula( :Project || :Item ) );

// Create separate judgment columns for the columns to be judged
// items = Design( dtbz:Item << get values(), <<levels )[2];
// items = dt << get column names( string, numeric );
Summarize( dtbz, items = by( :item ) );

// new column to justify the pass/no pass

For( i = 1, i <= N Items( items ), i++,
	it = items[i];
	
/*	dt1 << New Column( it || "—yield",
		formula(
					
			If(
				:Project == "A" & N Items( Loc( dtbz:combine << get as matrix(), "A" || it ) ) > 0,
					dtbz:USL[Loc( dtbz:combine << get as matrix(), "A" || it )[1]] <= Column( dt1, items[i] )
					 <= dtbz:USL[Loc( dtbz:combine << get as matrix(), "A" || it )[1]], 	
					
				:Project == "B" & N Items( Loc( dtbz:combine << get as matrix(), "B" || it ) ) > 0,
					dtbz:USL[Loc( dtbz:combine << get as matrix(), "B" || it )[1]] <= Column( dt1, items[i] )
					 <= dtbz:USL[Loc( dtbz:combine << get as matrix(), "B" || it )[1]], 	
					
				:Project == "C" & N Items( Loc( dtbz:combine << get as matrix(), "C" || it ) ) > 0,
					dtbz:USL[Loc( dtbz:combine << get as matrix(), "C" || it )[1]] <= Column( dt1, items[i] )
					 <= dtbz:USL[Loc( dtbz:combine << get as matrix(), "C" || it )[1]], 	
					
				:Project == "D" & N Items( Loc( dtbz:combine << get as matrix(), "D" || it ) ) > 0,
					dtbz:USL[Loc( dtbz:combine << get as matrix(), "D" || it )[1]] <= Column( dt1, items[i] )
					 <= dtbz:USL[Loc( dtbz:combine << get as matrix(), "D" || it )[1]], 			
			)
		)
	);*/


	Eval(
		Substitute(
				Expr(
					dt1 << New Column( it || "—yield",
						formula(
							curProject = :Project;
							Try( dtbzRow = (dtbz << get rows where( dtbz:Project == curProject & dtbz:item == __it__ ))[1], 0 );
							If( dtbzRow > 0,
								If( dtbz:LSL[dtbzRow] <= As Column( dt1, __it__ ) <= dtbz:USL[dtbzRow],
									1,
									0
								)
							);
						)
					)
				),
			Expr( __it__ ), it
		)
	);
);

[#3]

Thank you very much for your quick answer, it works.

[#4]

Hi  Jim， another small problem is that when the corresponding USL and LSL are not found in the specification table, the null value should be returned, but this code runs and returns 0. I don't know what is going on.

[#5]

The original modification I provided assumed that all Spec Limits would be available.  To allow for the potential of missing Limits, the formulas need to be changed to handle those cases.  The JSL below adjusts for a couple of possible cases, but you may need to add others, like:  

     The USL is less than the LSL

Names Default To Here( 1 );
//raw data
dt = Data Table( "raw data001" );

dt1 = dt << subset( all rows, all columns );

//spec table

dtbz = Data Table( "Spec" );

// Create separate judgment columns for the columns to be judged
// items = Design( dtbz:Item << get values(), <<levels )[2];
// items = dt << get column names( string, numeric );
Summarize( dtbz, items = by( :item ) );

// new column to justify the pass/no pass

For( i = 1, i <= N Items( items ), i++,
	it = items[i];

	Eval(
		Substitute(
				Expr(
					dt1 << New Column( it || "—yield",
						formula(
							curProject = :Project;
							dtbzRow = .;
							Try( dtbzRow = (dtbz << get rows where( dtbz:Project == curProject & dtbz:item == __it__ ))[1], 0 );
							Show( :project, dtbzRow );
							If( dtbzRow > 0 & Is Missing( dtbz:LSL[dtbzRow] ) == 0 & Is Missing( dtbz:USL[dtbzRow] ) == 0,
								If( dtbz:LSL[dtbzRow] <= As Column( dt1, __it__ ) <= dtbz:USL[dtbzRow],
									1,
									0
								),
								.
							);
						)
					)
				),
			Expr( __it__ ), it
		)
	);
);

[#6]

Thank you, but I have a question, why did the previous code have this problem? I see the logic of the previous code is: if found in the SPEC table, then the judgment, not found and there is no code, should be the empty value ah, why is still judging it?

[#7]

Calculated values persist from row to row, so if a condition in row 2 does not make a change to the calculated value, then the value that was calculated for row 1 remans unchanged.

[#8]

Thanks! I tried it and indeed under the previous code run, if it is not found in the SPEC table, it will go by the dtbzRow of the previous row. It's clearly not found, but it still presses the value of the previous row. I don't find this in the routine formulas, is it only happening when I use a lookup of another table?

[#9]

Memory variables in JMP remain persistent until changed.  Here is a script that creates a new column and formula that sets a variable "a" on row 1, and then changes it at row 10.  You can see the value of "a" does not change until row 10, but remains at the initial set value until then.

Names Default To Here( 1 );

dt = 
// Open Data Table: Big Class.jmp
// → Data Table( "Big Class" )
Open( "$SAMPLE_DATA/Big Class.jmp" );


// New column: Column 6
Data Table( "Big Class" ) << New Column( "test",
	Numeric,
	"Continuous",
	Format( "Best", 12 )
);

dt:test << set formula(
	If( Row() == 1, a = 1 );
	If( Row() == 10, a = a + 1 );
	:test = a;
);

[#10]

I see, it's because if it can't be found in the try statement, it won't be assigned to it, resulting in the original value, but it can't be found in the first line at the beginning, so how is it assigned?