243196_en-US

liliconcom

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

 

 

Re: How to get a pass_fail columnThanks for your help!
This code work very well !!
Thanks
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=""></ALL>

 

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

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

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