Solved: Re: To add spec limits in Variability chart from data table

tding001 · May 10, 2019 04:16 AM

Hi there,

My JMP version is 10.0.2. Due to work needed, I have hundreds of parameters to be analyzed and each parameters have more than 100 data points. My customer sent me another file including the LSL, target and USL of these parameters in excel. Therefore I am wondering if there is a script / embeded tool to link the spec limits with corresponding parameters when I am plotting the Variabiltiy chart (X axis shall be the different batch number and Y axis is the parameter data). I also want to show the box plot of the raw data. Currently I do all these manually which wastes a lot of time. So please let me know whether there is a solution (I assume should be)

Forgive me that I cannot show the raw data file due to confidential reason. Thank you in advance.

txnelson · May 13, 2019 10:49 AM

Your column names contain characters that JMP will try to execute, rather than use them as part of the name. Therefore I had to add in a :Name() function to force JMP to interpret the complete value as a name. See the script below.

Note: the dt = and dtLimits = statements have been changed to look for already opened data tables. You may need to change them back to using an open() function.

// Open table to have limits set on
dt = data table("raw data");
dtLimits = data table("Spec limit");


// Get a list of all of the numeric columns in the measurment data table
colNames = dt << get column names( numeric, string );

// Loop across the Limits table and apply the findings to data table
For( i = 1, i <= N Rows( dtLimits ), i++, 
	// If a column in the measurement data table has the same name as the current 
	// Limits data table column "Parameter" has, then process the data
	colNamesPosition = Loc( colNames, dtLimits:Parameter[i] );
	If( N Rows(colNamesPosition) > 0,
		Eval(
			Substitute(
					Expr(
						__col__ << set property(
							"Spec Limits",
							{LSL( __LSL__ ), USL( __USL__ ), Target( __Target__ ), Show Limits( 1 )}
						)
					),
				Expr( __col__ ), Parse( "dt:Name(\!"" || colNames[colNamesPosition[1]] || "\!")" ),
				Expr( __LSL__ ), dtLimits:LSL[i],
				Expr( __USL__ ), dtLimits:USL[i],
				Expr( __Target__ ), dtLimits:Target[i]
			)
		)
	);
);

// Run Variability Charts
Variability Chart( Y( :Name("TWNB_10/10$Vtlin%V"), :Name("TWNB_10/10$Idlin%uA/um") ), X( :wafer ) );

Jim

View solution in original post

txnelson · May 10, 2019 05:52 PM

Here is a sample script that will read in the values from a limits data table, and apply them to the measurement data table. And when the Variability Chart, or most other platform charts will display the spec limits.

// Open table to have limits set on
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
// Create a limits table for the example
dtLimits = New Table( "Limits",
	Add Rows( 2 ),
	New Column( "Parameter", Character, "Nominal", Set Values( {"height", "weight"} ) ),
	New Column( "LSL", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [52, 60] ) ),
	New Column( "Target", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [63, 105] ) ),
	New Column( "USL", Numeric, "Continuous", Format( "Best", 12 ), Set Values( [70, 180] ) )
);

// Get a list of all of the numeric columns in the measurment data table
colNames = dt << get column names( numeric, string );

// Loop across the Limits table and apply the findings to data table
For( i = 1, i <= N Rows( dtLimits ), i++, 
	// If a column in the measurement data table has the same name as the current 
	// Limits data table column "Parameter" has, then process the data
	colNamesPosition = Loc( colNames, dtLimits:Parameter[i] );
	If( N Rows() > 0,
		Eval(
			Substitute(
					Expr(
						__col__ << set property(
							"Spec Limits",
							{LSL( __LSL__ ), USL( __USL__ ), Target( __Target__ ), Show Limits( 1 )}
						)
					),
				Expr( __col__ ), Parse( "dt:" || colNames[colNamesPosition[1]] ),
				Expr( __LSL__ ), dtLimits:LSL[i],
				Expr( __USL__ ), dtLimits:USL[i],
				Expr( __Target__ ), dtLimits:Target[i]
			)
		)
	);
);

// Run Variability Charts
Variability Chart( Y( :height, :weight ), X( :sex ) );

Jim

tding001 · May 12, 2019 11:04 PM

Hi Nelson,

Thank you very much for your reply. In your sample you created two data tables, one is Big Class.jmp and another is the spec limits table. However in my case I already have these two data tables existed. So I think what left for me is just to link the two tables together and do what you provided in the script starting from colNames = dt << get column names (numeric, string ). I tried below:

dt=current data table();
show (dt);
dtLimits=Open( "$Desktop\JMP\Spec limits.jmp" );

and then attached

colNames = dt << get column names (numeric, string ).

and afterwards. Unfortunately only Spec limits.jmp was opened, but spec limits were not added into the current data table. Can you please help me to dubug? Really appreciate.

txnelson · May 12, 2019 11:33 PM

The statement:

dt=current data table();

sets the variable "dt" to point to an already opened data table. Therefore, if neither your measurements data table or your limits table are open, you will need to use the

dt=open("<path>/data table name");

to open your measurement data table, not the "current data table()" function.

You need to make sure you understand how the JSL I provided in my first response, rather than just cutting and pasting it into your code. All of the functions used in my example are documented in the Scripting Index, along with examples.

Help==>Scripting Index

Jim

tding001 · May 13, 2019 01:52 AM

Hi Neslon,

I think I know the problem, or at least the difference between my thinking and your script. Forgive me I didn't describe it clearly before.

At beginning I created a new data table with raw data pasted into. I assume this is the current data table. So I define it as dt=current data table(). Then I open the saved Spec limits.jmp file

dtLimits<span class="token operator">=</span><span class="token function">Open</span><span class="token punctuation">(</span> <span class="token string">"$Desktop\JMP\Spec limits.jmp"</span> <span class="token punctuation">)</span><span class="token punctuation">;</span>

I think this cannot work based on the script I posted?

What I am doing now is to save the new data table as raw data.jmp. Then use below script. But still no spec limits were added to the parameters.

// Open table to have limits set on
dt = Open( "C:\Users\Desktop\JMP/raw data.jmp" );
// Open spec limit table 
dtLimits = Open( "C:\Users\Desktop\JMP/Spec limits.jmp" );
// Get a list of all of the numeric columns in the measurement data table
colNames = dt << get column names( numeric, string );

// Loop across the Limits table and apply the findings to data table
For( i = 1, i <= N Rows( dtLimits ), i++, 
	// If a column in the measurement data table has the same name as the current 
	// Limits data table column "Parameter" has, then process the data
	colNamesPosition = Loc( colNames, dtLimits:Parameter[i] );
	If( N Rows() > 0,
		Eval(
			Substitute(
					Expr(
						__col__ << set property(
							"Spec Limits",
							{LSL( __LSL__ ), USL( __USL__ ), Target( __Target__ ), Show Limits( 1 )}
						)
					),
				Expr( __col__ ), Parse( "dt:" || colNames[colNamesPosition[1]] ),
				Expr( __LSL__ ), dtLimits:LSL[i],
				Expr( __USL__ ), dtLimits:USL[i],
				Expr( __Target__ ), dtLimits:Target[i]
			)
		)
	);
);

So I tried to debug this script; it turns out L22 shows error: invalid subscript (must be number or list of numbers). I am not sure why this works in your script but not in mine. If you have any ideas please let me know. Thank you.

				Expr( __col__ ), Parse( "dt:" || colNames[colNamesPosition[1]] ),

txnelson · May 13, 2019 02:05 AM

The issue is that the Loc() function is not finding a match in the colNames{} list. The function requires an exact match. Upper/Lower case, spacing etc. can all cause an issue.

Jim

tding001 · May 13, 2019 02:19 AM

The parameters in raw data.jmp are only parts of all the parameters in the spec limits.jmp. So this could be the reason?

I am really new to JSL so, if possible, please kindly let me know whether other functions can do the similar things based on my case. Thanks a lot for your help.

txnelson · May 13, 2019 07:09 AM

I just discovered an error in my code. Please change the line in the code

If( N Rows(  ) > 0,

to

If( N Rows( colNamesPosition ) > 0,

Jim

tding001 · May 13, 2019 07:24 AM

Thanks Nelson. I modified the script and this time another error poped out saying "scoped data table access requires a data table column or variable". This makes me confused again. Can you please let me know the reason?

txnelson · May 13, 2019 07:34 AM

The script I provided assumes that the Limits data table columns are named

Parameter
LSL
USL
Target

Are these the names of the columns in your Limits data table?

Would it be possible for you to attach a copy of your Limits data table and your Measurement data table. This would facilitate the debugging of the issues.

Jim