How to script to apply spec limit values appearing in a column to its own property based on labels on other columns?

[#1]

I have incoming data similar to the attached data table.

I would like to (as an example)

• save the row values appearing (first/top) in column Breakdown_5 against rows USL/LSL (under Label) for T5 (under TestStage) as limits in Breakdown_5 column property, similarly 
• save the row values appearing (first/top) in column Resistance_7 against rows USL/LSL (under Label) for S7 (under TestStage) as limits in Resistance_7 column property
• save the row values appearing (first/top) in column Current_7 against rows USL/LSL (under Label) for S7 (under TestStage) as limits in Current_7 column property
• save the row values appearing (first/top) in column Current_11 against rows USL/LSL (under Label) for W11 (under TestStage) as limits in Current_11 column property
• save the row values appearing (first/top) in column Capacitance_6 against rows USL/LSL (under Label) for U6 (under TestStage) as limits in Capacitance_6 column property

In general, how do I write a script in JSL to apply spec limits corresponding to the LSL/USL rows (under Label) appearing (first/at the top) under columns of measured data (Breakdown_#, Resistance_#, Current_# and so on) corresponding to correct TestStage#, where # refers to the test stage number.

[#2 · Accepted solution]

Here is a script that works with your sample data

Names Default To Here( 1 );
dt = Data Table( "exampleDataSummary" );

// Find all numeric columns and place in a list
numColList = dt << get column names( numeric, string );

// Find the unique values of TestStage
Summarize( dt, tstStage = By( :TestStage ) );

// Loop across all test stages and match with response column
stageNumber = "";
For Each( {Stage}, tstStage,
	stageNumber = Substr( Stage, 2 );
	resCol = "";
	For Each( {col}, numColList,
		If( Word( -1, col, "_" ) == stageNumber,
			resCol = col
		);
		// Get LSL and USL
		If( resCol != "",
			theLSL = Column( dt, resCol )[(dt << get rows where( :TestStage == Stage & :label == "LSL" ))[1]];
			theUSL = Column( dt, resCol )[(dt << get rows where( :TestStage == Stage & :label == "USL" ))[1]];
			Show( theLSL, theUSL );
			Eval(
				Substitute(
						Expr(
							Column( dt, resCol ) << set property( "Spec Limits", {LSL( _LSL_ ), USL( _USL_ )} )
						),
					Expr( _LSL_ ), theLSL,
					Expr( _USL_ ), theUSL				
						
					)
			);
		);
	 	
	);
);

[#3]

@txnelson  Yes, Thanks. The script works with the example data file. I have two questions.

1. Just realized that my measured parameter (column) names can be of the form Resistance_Null_Port_7, Capacitance_Full_Plate_6 etc. How to change the script when there are more than one underscores in the column names before the TestStage number comes at the end. I guess the implementation of the Word () function needs to change, but I am struggling with it (its a bit confusing not having used it before).

2. How to enable "Show As Graph Reference Lines" programmatically for each column once the Limits are applied? I know how to do this manually via Manage Spec Limits (). I am on JMP 16.2.

[#4]

Please go to the Scripting Index to find the details on the various functions that I used in the script I provided.  The Word() function, as I used it, does not care how many "_" are in the names.  The first term in the Word() function, as I am using it, is a -1, which instructs the function to take the first word, starting from the end of the string.

To tell JMP to automatically display the values as reference lines, the "Show()" parameter in the Spec Limits specification.

Column( dt, resCol ) << set property( "Spec Limits", {LSL( _LSL_ ), USL( _USL_ ), Show(1)} )

Please take the time to study the script I provided, to help you move ahead to becoming a more efficient scripter.

[#5]

@txnelson I am studying your code and thanks for explaining how Word () works. 

So far the script is not working for my actual data (which only has difference in the actual column names i.e. with more underscores).

The trouble is the script runs without throwing any errors on my actual data and there is nothing in the log unlike when I run the same script on the example data table attached here. So there is nothing for me to latch on to for debugging.

I had already tried show (1) to show the limits to enable "Show As Graph Reference Lines" programmatically. It does not work for me in JMP 16.2 using the example data posted here. 

I will try to debug the script and come back to you if I find anything. It would be useful to get some pointers for debugging in the absence of anything in the log. 

[#6 · Accepted solution]

@txnelson Needed to change 

Substr( )

for the script to work for my actual data. However, show (1)  still does not show the limits when a distribution is plotted for any measured parameter. 

The following also does not work on my JMP16.2 i.e. show limits does not enable "Show As Graph Reference Lines" in column properties.

dt=Open("$SAMPLE_DATA/Process Measurements.jmp");
Column(dt, "Process 2")<<Set Property(
			"Spec Limits",
			{LSL( 5 ), USL( 17 ), Target( 10 ), Show Limits( 1 )}
		);
Column(dt, "Process 3")<<Set Property(
			"Process Capability Distribution",
			Process Capability Distribution( Weibull )
		);

 However, this does the job.

Column( dt, resCol ) << set property( "Spec Limits", {LSL( _LSL_ ), USL( _USL_ ), Show Limits( 1 )})

[#7]

My mistake,  change 

     show(1)

to

     show limits(1)