cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
wjalford
Level III

Need to compare result from one table with spec limit in another table. If result < limit then pass

I have a table (Limits) with the limits.  The table columns are Product, Componet A, Componet B.  Under each componet is the limit.  Example Product, Aluminum, Zinc

                                                         Fudd         .24        .50

 

I have another table FG Metals that has a PASS/FAIL column and a Aluminum column and a Zinc column with the results in each row.  Example PASS/FAIL, Aluminum, Zinc

                                                                                .09        .1

I need to compare the dt(FG metals) Aluminum .09 result with the dt(Limits) Aluminum .24.  If result is less than limit then put Pass in the PASS/FAIL column otherwise put FAIL.

 

Hopefully I explained this well enough.  Please let me know if you have any questions.  

 

Wayne

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Need to compare result from one table with spec limit in another table. If result < limit th

 

  1. The various metals columns in the limits file need to be numeric columns.
  2.  The Pass/Fail column in the Metals data table needs to be defined as character.

Here is a script that works based upon the example data you supplied.....and with the above changes.  The code uses the limits data table to determine what metals to test.

Names Default To Here( 1 );
dtLimits = Data Table( "Limits sample (1)" );
dtMetals = Data Table( "FG Sample (1)" );

// Get the names of the columns with the metals data in them
metalsColNames = dtLimits << get column names( string, numeric );

For( theRow = 1, theRow <= N Rows( dtMetals ), theRow++,
	If( N Rows( dtLimits << get rows where( dtLimits:product == dtMetals:Product[theRow] ) ) > 0,
		theProductRow = (dtLimits << get rows where( dtLimits:product == dtMetals:Product[theRow] ))[1];
		result = "Pass";
		For( theColumn = 1, theColumn <= N Items( metalsColNames ), theColumn++,
			target = Column( dtLimits, metalsColNames[theColumn] )[theProductRow];
			col = Column( dtMetals, metalsColNames[theColumn] );
			If( col[theRow] < target,
				result = "Fail"
			);
		);
		dtMetals:Name( "Pass/Fail" )[theRow] = result;
	)
);
Jim

View solution in original post

21 REPLIES 21
wjalford
Level III

Re: Need to compare result from one table with spec limit in another table. If result < limit th

I have attached sample files.

txnelson
Super User

Re: Need to compare result from one table with spec limit in another table. If result < limit th

Here is a script that I think does what you want

passfail.PNG

Names Default To Here( 1 );

// Create example data tables
dtLimits = New Table( "Limits",
	New Column( "Product", character, values( {"Fudd"} ) ),
	New Column( "Aluminum", values( {.24} ) ),
	New Column( "Zinc", values( {.50} ) )
);

dtMetals = New Table( "FG Metals",
	add rows( 10 ),
	New Column( "Pass/Fail Aluminum", character ),
	New Column( "Pass/Fail Zinc", character ),
	New Column( "Aluminum", formula( Random Uniform( 0, 1 ) ) ),
	New Column( "Zinc", formula( Random Uniform( 0, 1 ) ) )
);

// Get the names of the columns with the metals data in them
metalsColNames = dtMetals << get column names( striing, numeric );

// Loop across the columns and down the rows and calculate the pass/fail
For( theColumn = 1, theColumn <= N Items( metalsColNames ), theColumn++,
	target = Column( dtLimits, metalsColNames[theColumn] )[1];
	col = Column( dtMetals, metalsColNames[theColumn] );
	passFail = Column( dtMetals, " Pass/Fail " || metalsColNames[theColumn] );
	For( theRow = 1, theRow <= N Rows( dtMetals ), theRow++,
		If( col[theRow] < target,
			passFail[theRow] = "Fail",
			passFail[theRow] = "Pass"
		)
	);
);
Jim
wjalford
Level III

Re: Need to compare result from one table with spec limit in another table. If result < limit th

JIM,
In my limit file I have multiple products. Fudd with Aluminum and the limit .05 then Miracle with Aluminum and the limit .02. Each product in the limits file is under the product column. Example Product, Aluminum, Zinc
Fudd .5 .1
Miracle .2 .5
Please let me know how to handle the product.

Regards,

Wayne
txnelson
Super User

Re: Need to compare result from one table with spec limit in another table. If result < limit th

All that has to happen is to find the correct row in the Limits Table, for the Product on the row in the Metals data table. I will get you a modification of my script that will do that......but don't you need to have more than one Pass/Fail column in your metals data table, because each metal can either pass or fail.....or are you saying that if any metal fails, then it is a fail, but if they all pass then the row passes????
Jim
wjalford
Level III

Re: Need to compare result from one table with spec limit in another table. If result < limit th

If any metal fails, then the sample fails. If all metals pass, then the sample passes.
txnelson
Super User

Re: Need to compare result from one table with spec limit in another table. If result < limit th

 

  1. The various metals columns in the limits file need to be numeric columns.
  2.  The Pass/Fail column in the Metals data table needs to be defined as character.

Here is a script that works based upon the example data you supplied.....and with the above changes.  The code uses the limits data table to determine what metals to test.

Names Default To Here( 1 );
dtLimits = Data Table( "Limits sample (1)" );
dtMetals = Data Table( "FG Sample (1)" );

// Get the names of the columns with the metals data in them
metalsColNames = dtLimits << get column names( string, numeric );

For( theRow = 1, theRow <= N Rows( dtMetals ), theRow++,
	If( N Rows( dtLimits << get rows where( dtLimits:product == dtMetals:Product[theRow] ) ) > 0,
		theProductRow = (dtLimits << get rows where( dtLimits:product == dtMetals:Product[theRow] ))[1];
		result = "Pass";
		For( theColumn = 1, theColumn <= N Items( metalsColNames ), theColumn++,
			target = Column( dtLimits, metalsColNames[theColumn] )[theProductRow];
			col = Column( dtMetals, metalsColNames[theColumn] );
			If( col[theRow] < target,
				result = "Fail"
			);
		);
		dtMetals:Name( "Pass/Fail" )[theRow] = result;
	)
);
Jim
wjalford
Level III

Re: Need to compare result from one table with spec limit in another table. If result < limit th

Jim,

 

Works great.  Thanks again for your help.

 

Wayne

HSS
HSS
Level IV

Re: Need to compare result from one table with spec limit in another table. If result < limit th

Hello TxNelson,

 

I need similar solution and I was trying to follow the one you have provided but not able to get my results. Could you please help me --

 

I have two tables -- one is data table and other is specs table. I need to compare data in main table with the specs in specs table and put it into a new column as 1 (pass) and 0 (fail).

 

Any help ?

 

 

 
txnelson
Super User

Re: Need to compare result from one table with spec limit in another table. If result < limit th

I have placed below a solution to your request.  However, you need to take the time to read the Scripting Guide, to make sure you understand what I have provided, and also to assist you in understanding the JSL provided in other Discussions

names default to here(1);

dtspecs = Data Table ("Specs_Limits");

dt = Data Table ("Table_A");

Loadlimit = dt << Manage Spec Limits( Y( :BVIA_RES, :COILVIA_RES, :RDR_RES, :TAD_RES, 
:WELGL_RES), Load from Limits Table("Specs_Limits.jmp"), Save to Column Properties (1) );

Loadlimit << close window;

colNamesList = dt << get column names(string,continuous);

For( theColumn = 1, theColumn <= N Items( colNamesList ), theColumn++, 

	Clear Symbols( specs );
	specs = Column( dt, colNamesList[theColumn] ) << get property( "Spec Limits" );
	If( Is Empty( specs ) == 0,
		Eval(
			Substitute(
					Expr(
		
						dt << New Column( colNamesList[theColumn] || " PASS/Fail",
							numeric,
							CONTINUOUS,
							FORMULA( If( __LSL__ <= __col__ <= __USL__, 1, 0 ) )
						);
						Column( colNamesList[theColumn] || " PASS/Fail" ) << delete formula;
					),
				Expr( __LSL__ ), specs["LSL"],
				Expr( __col__ ), Parse(":" || colNamesLIst[theColumn] ),
				Expr( __USL__ ), specs["USL"]
			)
		)
	);
);
Jim