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

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

wjalford — Fri, 01 Feb 2019 21:01:03 GMT

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

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

wjalford — Fri, 01 Feb 2019 21:11:22 GMT

I have attached sample files.

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

txnelson — Fri, 01 Feb 2019 21:31:36 GMT

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

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

wjalford — Fri, 01 Feb 2019 22:03:08 GMT

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

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

txnelson — Fri, 01 Feb 2019 22:45:29 GMT

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

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

wjalford — Fri, 01 Feb 2019 23:12:07 GMT

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

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

txnelson — Sat, 02 Feb 2019 02:11:37 GMT

The various metals columns in the limits file need to be numeric columns.
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;
	)
);

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

wjalford — Sun, 03 Feb 2019 15:58:04 GMT

Jim,

Works great. Thanks again for your help.

Wayne

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

HSS — Fri, 31 Jan 2020 12:57:25 GMT

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 ?

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

txnelson — Fri, 31 Jan 2020 13:57:33 GMT

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

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

HSS — Fri, 31 Jan 2020 14:20:17 GMT

Hi, Many thanks,

Yes, that is what (reading Scripting Guide) I am doing now a day. :)

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

HSS — Mon, 03 Feb 2020 10:49:59 GMT

Hi Nelson,

I an getting following error in the script you send me on Friday. Any help

Also why do we use "__" in specs limits like LSL. I was trying to find answer in jmp script guideline but could not understand much.

Thanks.

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

txnelson — Mon, 03 Feb 2020 14:11:58 GMT

The line of code that is having an issue, is

Column( colNamesList[theColumn] || " PASS/Fail" ) << delete formula;

The error statement is saying that it can not find the column that is being referenced. The column being referenced should have been created in the statement just before.

dt << New Column( colNamesList[theColumn] || " PASS/Fail",
							numeric,
							CONTINUOUS,
							FORMULA( If( __LSL__ <= __col__ <= __USL__, 1, 0 ) )
						);

What is required for this statement to work, is that both an LSL and a USL exist for the column being referenced in the formula. I suspect this is the issue. I would check to make sure that both LSL and USL exist.

Concerning your question

"Also why do we use "__" in specs limits like LSL"

This is not a JMP requirement. In the Substitute() function, there is a requirement to have pairs of matching values that JMP is going to use for the substitution. I use the "__" before and after the matching values, just to make the code clear. I also give the name of the matching pair something meaningful. And in this case, the Substitution() function is substituting the LSL, the reference Col and the USL, and so by my convention, I use, __LSL__, __col__ and __USL__.

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

HSS — Mon, 03 Feb 2020 14:26:48 GMT

Hi,

I did that check and made sure that LSL and USL both should be there in specs table. In fact, I also start the For loop -- the Column = 10 where I knew that from column 10th onward, I have specs limits in the table, though it should be taken care by Is Empty statement.

Nothing worked.

Thanks.

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

txnelson — Mon, 03 Feb 2020 14:48:28 GMT

The Manage Spec Limits, may not be finishing before the code below is processing. Try running just through the Manage Spec Limits. Then once it is complete, run the remaining code.

Also, add

show(colNamesList[theColumn] || " PASS/Fail");

Just before line 19

		Eval(

Then take a look at what name the code is complaining about

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

HSS — Mon, 03 Feb 2020 15:37:26 GMT

Hi, Yes it is working if I am running code till manage spec limit first and then the rest of it, And adding

show(colNamesList[theColumn] || " PASS/Fail"); does not make any difference. Code is still not running in one go, even after adding this line.

Is there any way I can force to evaluate manage specs limit part first and then move further or can I put some wait (say - 2 sec) commend before going into the for loop ?

Thanks.

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

txnelson — Mon, 03 Feb 2020 15:41:48 GMT

You can add a

wait(0);

dt << rerun formulas;

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

HSS — Mon, 03 Feb 2020 16:01:43 GMT

dt << rerun formulas; and wait (0) - none of them is working for me. But yes, if I split my code in two runs -- it is working fine.

Any other suggestion ?

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

HSS — Mon, 03 Feb 2020 16:09:03 GMT

Whatever the wait time I am putting, it just evaluate only first column and through the same error. But, if I split and run it in two parts -- it is fine ! No idea why ?

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

HSS — Mon, 03 Feb 2020 16:25:34 GMT

Hi TxNelson,

In the script, if I comment out -- Column( colNamesList[theColumn] || " PASS/Fail" ) << delete formula; it is running fine.

Just a query -- are we not deleting the column formula, even before we are getting our column? As our column will be complete

once we will substitute the values of LSL and USL and we are doing in in the next line of For loop ?

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, /*show(colNamesList[theColumn] || " PASS/Fail"), Wait (1); */
		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"]
			)
		)
	);
);