cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Song
Level III

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

Image.jpg

Hi, I have one question.

Please refer to the attachment(just samples).

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

Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

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

View solution in original post

9 REPLIES 9
MichelleG
Level III

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.
Song
Level III

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!
MichelleG
Level III

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.
Song
Level III

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 :)
MichelleG
Level III

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.

txnelson
Super User

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 );
Jim
Song
Level III

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!
Song
Level III

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!
Song
Level III

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.