topic Re: How to get a pass_fail column in Discussions

How to get a pass_fail column

Song — Tue, 28 Jan 2020 08:07:37 GMT

I'd like to get a pass_fail column, and @txnelson made it to script.

But, this script does not work if any of the many columns do not have a spec in the middle.

How can i fix it?

Names Default To Here( 1 );
dtOrig = current data table();

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

// Create a work table of just the continuous columns
dt = dtOrig << subset( columns( colNamesList ), selected rows( 0 ) );

// Loop across all of the columns and apply a Range Check to eliminate
// all data outside of the upper and lower spec limits
For( theCol = 1, theCol <= N Items( colNamesList ), theCol++,
	specs = Column( dt, colNamesList[theCol] ) << get property( "spec limits" );
	Eval(
		Substitute(
				Expr(
					Column( dt, colNamesList[theCol] ) << set property(
						"Range Check",
						(LELE( __LSL__, __USL__ ))
					)
				),
			Expr( __LSL__ ), specs["LSL"],
			Expr( __USL__ ), specs["USL"]
		)
	);
	
	// Now convert the column to numeric	
	Column( dt, colNamesList[theCol] ) << data type( character );
		
	// Now find all cells where values were eliminated
	dt << select where( As Column( dt, colNamesList[theCol] ) == "" );
	
	// If non passing rows were found, process the column to change the passing rows to
	// a blank, and non passing to the name of the column
	If( N Rows( dt << get selected rows ) > 0,
		// Now set all eliminated cells to have the value of the name of the column
		Column( dt, colNamesList[theCol] )[dt << get selected rows] = Column( dt, colNamesList[theCol] ) <<
		get name;
		// Invert the row selection, to select all rows where values passed and set them to a blank
		dt << invert row selection;
		Column( dt, colNamesList[theCol] )[dt << get selected rows] = "";
	,	// else set all 
		Column( dt, colNamesList[theCol] )[Index( 1, N Rows( dt ) )] = ""
	);
);

// Now create a new column to hold the information on either that the row Passed or 
// which columns failed
dt << New Column( "Pass_Fail", Character );

// Loop across all rows and set the values for the new column
For( theRow = 1, theRow <= N Rows( dt ), theRow++,
	If( Trim( Concat Items( dt[theRow, 0, ""] ) ) == "",
		:Pass_Fail[theRow] = "Pass"
	,
		thePass_Fail = Concat Items( dt[theRow, 0], "," );
		For( i = Length( thePass_Fail ), i >= 2, i--,
			If( Substr( thePass_Fail, i, 1 ) == ",",
				If( Substr( thePass_Fail, i - 1, 1 ) == ",",
					thePass_Fail = Substr( thePass_Fail, 1, i - 1 ) || Substr( thePass_Fail, i + 1 )
				)
			)
		);
		If( Right( thePass_Fail, 1) == ",", thePass_Fail = Left( thePass_Fail, length( thePass_Fail ) - 1 ) );
		:Pass_Fail[theRow] = thePass_Fail;
	)
);

// Only the new column is what is needed, so delete all other columns
dt << delete columns( eval(colNamesList) );

// Add the new column to the original data table
dtOrig << Update(
	With( dt )
);

// Delete the no longer needed work table
close(dt, nosave );

Re: How to get a pass_fail column

Byron_JMP — Thu, 30 Jan 2020 15:59:11 GMT

TXnelson writes really fabulous code!

However, maybe a you are looking for a simple solution like:

If( :Data >= 42,"Too High",If( :Data < 7,"Too Low","Just Right"))

this is just a nested If statement if the data values is 42 or higher the formula returns, "Too High", then if data is less than 7 it returns "Too Low", and if its neither too high or too low, then the formula returns "Just Right"

You can paste the formula into the formula editor, and then substitute in the correct column name, values and messages. (Right click on the column name, click on formula , and past the above JSL into the dialog)

Cheers,

Re: How to get a pass_fail column

Mark_Bailey — Thu, 30 Jan 2020 16:43:50 GMT

I like simple, like me!

So I simplified it (no nesting required):

If( :Data >= 42, "Too High", :Data < 7, "Too Low", "Just Right")

Re: How to get a pass_fail column

txnelson — Thu, 30 Jan 2020 17:02:17 GMT

I need to thank @Byron_JMP for responding. I had intended to respond to this discussion, and before I did, it moved on to an older page and thus it fell out of my memory. Byron's response kicked the discussion back into my NameSpace.
I believe all you have to do to fix your issue, is to validate that a given column has spec limits associated with it. And, if no spec limits are found, then do not process that column. Below is a somewhat cryptic suggested change that may do what you want. The basics of the issue is to use an If() function to only process the code when the spec limits are found This is a replacement for your current specs = line of code

clear symbols(specs);
specs = Column( dt, colNamesList[theCol] ) << get property( "spec limits" );
If(isEmpty(specs)==0,
	<all of the code to the end of the For loop
	);

Re: How to get a pass_fail column

Song — Fri, 31 Jan 2020 04:48:29 GMT

Thanks for your help!
This code work very well !!
Thanks :)