BookmarkSubscribeRSS Feed
Choose Language Hide Translation Bar
wjalford

Contributor

Joined:

Jan 16, 2019

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

Joined:

Jun 22, 2012

Solution

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
7 REPLIES 7
wjalford

Contributor

Joined:

Jan 16, 2019

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

Joined:

Jun 22, 2012

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

Contributor

Joined:

Jan 16, 2019

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

Joined:

Jun 22, 2012

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

Contributor

Joined:

Jan 16, 2019

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

Joined:

Jun 22, 2012

Solution

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

Contributor

Joined:

Jan 16, 2019

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