227796_en-US

liliconcom

When a spec is on, how do I write a script when I want to write a fail name?

Hi, I have one question.

When a spec is on, how do I write a script when I want to write a fail name? like above image.

Please refer to the attachment(just samples).

In the actual data I write, there are 1000 columns. 

Thanks

Re: When a spec is on, how do I write a script when I want to write a fail name?*remind
@txnelson
Wow, thank you, I have one more question.
If only the middle 100 of the 1,000 columns don't have specs or no value, this script won't work. Can you solve it?
And I want the Pass fail column to be at the front.
Thanks a lot for your help!Re: When a spec is on, how do I write a script when I want to write a fail name?Thanks a lot MichelleG!!
This script was a great help to me.
Re: When a spec is on, how do I write a script when I want to write a fail name?Wow, thank you, I have one more question.
If only the middle 100 of the 1,000 columns don't have specs, this script won't work. Can you solve it?
And I want the Pass fail column to be at the front.
Thanks a lot for your help!Re: When a spec is on, how do I write a script when I want to write a fail name?

Here is my take on using the Spec Limits in each columns' Column Property to test each row.  The method used for the testing is to apply a Range Check to each column.  This is used because it is a very efficient way to deal with all of the data in a column with one operation.  Given the number of columns you are dealing with, this method should be more efficient than testing each cell.  In the script I use the Semiconductor Data Table from the Sample Data folder.  It has all spec limits set for all 128 measurement columns.

Names Default To Here( 1 );
dtOrig = Open("$SAMPLE_DATA/Semiconductor Capability.jmp");

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: When a spec is on, how do I write a script when I want to write a fail name?

Okay, I'm not familiar with that add-in but perhaps its author @txnelson can speak to how to integrate information from that add-in here. I've written a little example that would do what you're looking for, tested against your 7-column example table, and I hope it is extensible to your 1000-column version. I'm assuming that we have a list of upper limits (UL) and a list of lower limits (LL) that are in the same order as the columns. You can read in a column from a data table, if needed, I just don't know where to access the data table from the add-in.

 

There are many different ways you could implement this. What I've written is just one way. The general plan I've used is: check each column against its respective limits. If it is out of spec, append to a list noting this. At the end of the loop, if the list is empty, write PASS. Otherwise, concatenate all the items in the list.

 

dt = current data table(); // Process Measurements.jmp

// I just made these up.
// Assuming they are in the same order as the column names
ul = {10.5, 13.8, 2, 5, 1.1, 0.8, 11.8};
ll = {5.5, 13.4, 0.1, 1, 0.2, 0.2, 7};

col_list = dt << get column names(String);
dt << New Column("Check", Character, Nominal); // make blank column
// note that the new column is not in col_list

For Each Row( // in current data table
 failcheck = {}; // initialize empty list
 For(c=1, c<=N Items(col_list), c++, // loop over columns in col_list
  // check if lower than LL or higher than UL
  If(column(dt,col_list[c])[] < ll[c] | column(dt,col_list[c])[] > ul[c],
   // if so, add this column to the failcheck list
   Insert Into(failcheck, col_list[c])
  )
 );
 If(N Items(failcheck)==0, // is the list empty?
  column(dt,"Check")[] = "PASS",
  column(dt,"Check")[] = Concat Items(failcheck,", ")
 );
);

Does this make sense? Let me know if you have any questions about what is going on.

Re: When a spec is on, how do I write a script when I want to write a fail name?Yes! that' right.
If every colunm is within spec, i want to just write "PASS".
There is no limit. If every single one is bad, i want to write all listed.
I add LSL, USL spec using the Add-in program shown below.
https://community.jmp.com/t5/JMP-Add-Ins/Write-Limits-to-a-Data-Table-from-a-Limits-Table/ta-p/35790
Thanks a lot for your help Re: When a spec is on, how do I write a script when I want to write a fail name?Okay, you want to add a column where it lists all the Process columns that spec out. That is, you want to check each column and append to a string for that row based on whether or not a condition is bad for that column's data. Right? If every column is within spec, what should it do? Is there a limit to how many column names you want listed, or do you want potentially all 7 (or 1000) listed if every single one is bad?

How do you know which columns spec out? I'm just asking for the mathematical operation/s, not the exact numbers. I suspect there is a unique spec limit (or limits) for each column. For 7 columns, we could just define it in an array we loop over, but for 1000 columns you are probably going to want it in a reference table that you read in.Re: When a spec is on, how do I write a script when I want to write a fail name?Attachment has 7 coulmns and i want to add one coulmn in order to check what columns spec out.
I just wanted to let you know that there are 1000 columns of data that I write in the company and i can't upload that because of confidential.
If you let me know how to do this on the attachment, i will edit it on my data.
Thanks!
Re: When a spec is on, how do I write a script when I want to write a fail name?What are you doing in order to determine the fail name you want to write? That is necessary to know in order to help you write a script to do it. It looks like you want to label the row based on some finding across these 1000 Process columns.