topic Re: When a spec is on, how do I write a script when I want to write a fail name? in Discussions

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

Song — Tue, 01 Oct 2019 15:06:45 GMT

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?

MichelleG — Tue, 01 Oct 2019 22:08:15 GMT

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.

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

Song — Wed, 02 Oct 2019 00:02:19 GMT

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?

MichelleG — Wed, 02 Oct 2019 00:11:26 GMT

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?

Song — Wed, 02 Oct 2019 06:04:59 GMT

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?

MichelleG — Wed, 02 Oct 2019 17:22:13 GMT

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?

txnelson — Thu, 03 Oct 2019 09:14:10 GMT

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?

Song — Mon, 07 Oct 2019 05:50:47 GMT

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?

Song — Mon, 07 Oct 2019 05:52:28 GMT

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?

Song — Mon, 14 Oct 2019 09:08:17 GMT

*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!