News
On June 1, we’re asking you to select a content label when starting a new topic in the Discussions area. Read more to find out why.
Choose Language Hide Translation Bar
Highlighted
Song
Level III

How to get a pass_fail column

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

 

 

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
txnelson
Super User

Re: How to get a pass_fail column

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

 

Jim

View solution in original post

0 Kudos
4 REPLIES 4
Highlighted
Byron_JMP
Staff

Re: How to get a pass_fail column

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

Screen Shot 2020-01-30 at 10.55.37 AM.png

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,

B

JMP Systems Engineer, Pharm and BioPharm Sciences
0 Kudos
Highlighted

Re: How to get a pass_fail column

I like simple, like me!

 

So I simplified it (no nesting required):

 

If( :Data >= 42, "Too High", :Data < 7, "Too Low", "Just Right")
Learn it once, use it forever!
0 Kudos
Highlighted
txnelson
Super User

Re: How to get a pass_fail column

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

 

Jim

View solution in original post

0 Kudos
Highlighted
Song
Level III

Re: How to get a pass_fail column

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