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

[#1]

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

[#2]

I have attached sample files.

[#3]

Here is a script that I think does what you want

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

[#4]

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

[#5]

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????

[#6]

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

[#7 · Accepted solution]

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

[#8]

Jim,

Works great.  Thanks again for your help.

Wayne

[#9]

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 ?

[#10]

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