topic Re: How the pass/fail formula works？ in Discussions

How the pass/fail formula works？

lehaofeng — Tue, 01 Jul 2025 19:55:47 GMT

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]], 	
					
						
						
			)
		)
	)
);

Re: How the pass/fail formula works？

txnelson — Tue, 27 Jun 2023 06:27:58 GMT

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
		)
	);
);

Re: How the pass/fail formula works？

lehaofeng — Tue, 27 Jun 2023 06:48:44 GMT

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

Re: How the pass/fail formula works？

lehaofeng — Tue, 27 Jun 2023 08:03:06 GMT

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.

Re: How the pass/fail formula works？

txnelson — Tue, 27 Jun 2023 13:30:15 GMT

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
		)
	);
);

Re: How the pass/fail formula works？

lehaofeng — Tue, 27 Jun 2023 13:36:09 GMT

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?

Re: How the pass/fail formula works？

txnelson — Tue, 27 Jun 2023 14:05:25 GMT

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.

Re: How the pass/fail formula works？

lehaofeng — Wed, 28 Jun 2023 01:26:53 GMT

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?

Re: How the pass/fail formula works？

txnelson — Wed, 28 Jun 2023 01:41:01 GMT

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;
);

Re: How the pass/fail formula works？

lehaofeng — Wed, 28 Jun 2023 01:41:12 GMT

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?

Re: How the pass/fail formula works？

txnelson — Wed, 28 Jun 2023 02:36:57 GMT

I annotated the script in an effort to make the reading of it easier. I also made a minor change to the Try() function, moving it to providing a result to dtbzRow.

See if this helps in the underastanding

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;  // Get the value of the current row
							                        // in the raw data table
							dtbzRow = .;  // dtbzRow is the row in the spec table where
							              // the spec limits are for the current Project
							              // and the current Item  Initialize it to 
							              // a missing value so any previous value is
							              // removed
							// Set the dtbzRow value to the spec table row where the
							// limits for the Project and Item are found.
							// If no such row is in the spec table, set the value
							// to 0
							dtbzRow = Try(
								(dtbz << get rows where(
									dtbz:Project == curProject & dtbz:item == __it__
								))[1],
								0
							);
							
							// If a spec row is found, and there are both USL and LSL
							// limits then set the yield value.  If not found the 
							// yield value returned is a missing value
							If(
								dtbzRow > 0 & Is Missing( dtbz:LSL[dtbzRow] ) == 0 &
								Is Missing( dtbz:USL[dtbzRow] ) == 0,
								// If the column's value is between the LSL and USL
								// the value is set to 1, if outside one of the 
								// limits it is set to 0 
								If(
									dtbz:LSL[dtbzRow] <= As Column( dt1, __it__ ) <= dtbz
									:USL[dtbzRow],
									1,
									0
								),
								.
							);
						)
					)
				),
			Expr( __it__ ), it
		)
	);
);

Re: How the pass/fail formula works？

lehaofeng — Wed, 28 Jun 2023 02:39:56 GMT

I understand, thanks for your patience!