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
tatarjj
Level I

Script works in scripting editor but not on menu item (Get Column Names not behaving the same)

When I run the following script from the scripting editor, it works fine.  But when I run it from a custom menu item I created in View->Customize, it does not work.  I've traced it down to the Get Column Names function not behaving the same.  When I put a print statement to output the value of each column name and its Type before I use Get Property on it, I see this (for example) when running from the scripting editor:

 

:exec_typ

"Name"

 

HOWEVER, when I run it from my menu item, I get this:

 

exec_typ

"Name"

 

Notice the lack of the colon.  (Whatever the colon does, I don't know.)


Ultimately, the script crashes because when I try to run the Get Property function on the column named exec_typ, JMP doesn't recognize it as a column name or something like that:

 

Send Expects Scriptable Object{7} in access or evaluation of 'Send' , colNames[colInd] <<  /*###*/Get Property( "Spec limits" ) /*###*/

 

 

Any idea what the heck is going on?  Why does the Get Column Names function behave differently if called from the scripting editor vs from a menu shortcut?

Code below.  Yes, some of it is messy.

 

//Reports all failing tests for a unit. Not temperature-smart (yet!)
dt = Current Data Table();

colNames = dt << get column names(Numeric);


//I need to clean up the add columns scripts... once I understand how it works.
dt << add multiple columns(
 "Num Tests Failing",
 1,
 After( "20:Neg_Contact:Cont_int2[1]" ),
 numeric( 10 )

);
colf = Column( "Num Tests Failing" );
colf << Modeling Type ( Nominal );


dt << add multiple columns(
 "Failing Tests",
 1,
 After( "20:Neg_Contact:Cont_int2[1]" ),
 numeric( 10 )

);
colf = Column( "Failing Tests" );
colf << Data type( Character );



For (i = 1, i <= N Rows(dt), i++,

 Row() = i;

 failingTests = "";
 NumOfFailingTests = 0;
 If (:Name("HBIN_NAM") != "Good" & :Name("HBIN_NAM") != "1:Good" , //It's a fail
 
 //Efficiency could be improved by extracting all spec limits first.
 For (colInd = 1, colInd <= N Items(colNames), colInd++,

 //print statements for debugging.
 print(colNames[colInd]);
 print(Type(colNames[colInd]));
 
 //THIS IS WHERE IT KEEPS CRASHING
 specLimits = colNames[colInd] << Get Property ("Spec limits");

 LSL_Char = Char(specLimits[1]);
 USL_Char = Char(specLimits[2]);
 
 LSL_Char = SubStr(LSL_Char, 5, (Length(LSL_Char) - 5));
 USL_Char = SubStr(USL_Char, 5, (Length(USL_Char) - 5));
 
 
 LSL_Num = 0; //initializing value to set its scope; is this necessary? Donno.
 USL_Num = 0;
 
 
 If(LSL_Char == "." | LSL_Char == "", 
 LSL_Char = "NO LIMIT",
 //else
 LSL_Num = Num(LSL_Char);
 );
 If(USL_Char == "." | USL_Char == "", 
 USL_Char = "NO LIMIT",
 //else
 USL_Num = Num(USL_Char);
 );
 
 failedTest = 0;
 
 
 If(LSL_Char != "NO LIMIT",
 If( colNames[colInd][i] < LSL_Num,
 failedTest = 1;
 NumOfFailingTests = NumOfFailingTests + 1;
 ); 
 );
 If(USL_Char != "NO LIMIT",
 If( colNames[colInd][i] > USL_Num,
 failedTest = 1;
 NumOfFailingTests = NumOfFailingTests + 1;
 ); 
 ); 
 
 If(failedTest == 1,
 failingTests = failingTests || Char(colNames[colInd]) || "; ";
 ); 
 
 );
 );
 :Name( "Failing Tests" )[i] = failingTests;
 :Name( "Num Tests Failing" )[i] = NumOfFailingTests;



);

 

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Script works in scripting editor but not on menu item (Get Column Names not behaving the same)

My assumption is that since you are not specifically scoping your variables (:), that you are running into issues when trying to run in the 2 different environments.  The other item I observed is that your method is going to result in a very slow piece of code.  Given that, I have rewritten your script, using some methods that I believe will work in both environments, and will also run much faster.  The script below run against the Semiconductor Capability data table that is deistributed with JMP.  But if you just strip out the reference to opening that table, along with the statement following the Open of the data table and replace them with your

     dt = current data table();

the code should work on your data.

I have annotated the code below, so I think you should be able to follow it.  Also, I VERY STRONGLY SUGGEST that you read the Scripting Guide distributed with your install of JMP.

     Help==>Books==>Scripting Guide

It will tell you all about what those crazy colons are all about

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );

dt << delete columns( {2, 3, 4} );

// Get the numeric column names as strings
colNames = dt << get column names( Numeric, string );

// Create the new calculated columns
// They will be populated later
dt << New Column( "Num Tests Failing", numeric );
dt << New Column( "Failing Tests", character );

// Create a new column for each column in the colNames list
// and populate the colNames name, if it does NOT pass
For( i = 1, i <= N Items( colNames ), i++,
	// Create the new colum
	dt << New Column( "PassFail " || Char( i ), character );
	// Get the Spec Limits for the first column
	specLimits = Column( dt, colNames[i] ) << get property( "Spec Limits" );
	// If no Spec Limits are found, do not process
	If( Is Empty( specLimits ) == 0,
		// Get the USL and LSL and if not present set to missing
		LSL = Try( specLimits["LSL"], . );
		USL = Try( specLimits["USL"], . );
		// If the LSL is not missing process
		If( Is Missing( LSL ) == 0,
			// Find all rows where the colNames column is less than
			// the LSL
			dt << select where( As Column( dt, colNames[i] ) < LSL );
			// If any rows are selected, then process
			If( N Rows( dt << get selected rows ) > 0,
				// Set the value in the PassFail column to the name of
				// the colNames being processed.  This will allow for 
				// the counting and the concatenating for the new
				// calculation columns
				Column( dt, "PassFail " || Char( i ) )[dt << get selected rows] = colNames[i]
				 || "; "
			);
		);
		// See above comments for LSL
		If( Is Missing( USL ) == 0,
			dt << select where( As Column( dt, colNames[i] ) > USL );
			If( N Rows( dt << get selected rows ) > 0,
				Column( dt, "PassFail " || Char( i ) )[dt << get selected rows] = colNames[i]
				 || "; "
			);
		);
	);
);

// Clean up and selected columns 
dt << clear select;

// Create the command string required for the formula for the
// Failing Tests, which is a concatenated string of all of the
// PassFail columns.  
theExpr = "dt:Failing Tests << set formula(Concat(:PassFail 1";
For( i = 2, i <= N Items( colNames ), i++,
	theExpr = theExpr || ",:PassFail " || Char( i )
);
theExpr = theExpr || "))";
// Execute the command string
Eval( Parse( theExpr ) );
// Delete the formula, which will remove the formula, but not
// the calculated values
dt:Failing Tests << delete formula;

// Create the command string for the Num Tests Failing column
// The count is the sum of the "True" Boolean comparisions.
// That is, if PassFail 1 != "", it will be "True" and therefore
// will have the value of 1.  If the cell is blank, it will have a 
// value of 0.  The sum of all of these comparisons will be equal to
// the number of non passing parameters(tests)
theExpr = "dt:Num Tests Failing << set formula(sum(:PassFail 1 != \!"\!"";
For( i = 2, i <= N Items( colNames ), i++,
	theExpr = theExpr || ",:PassFail " || Char( I ) || " != \!"\!""
);
theExpr = theExpr || "));";
Eval( Parse( theExpr ) );
dt:Num Tests Failing << delete formula;

// Delete all of the PassFail columns, just to clean up the data table
dt << delete columns(index(n cols(dt)-N items(colNames)+1,ncols(dt)));
Jim

View solution in original post

4 REPLIES 4
txnelson
Super User

Re: Script works in scripting editor but not on menu item (Get Column Names not behaving the same)

My assumption is that since you are not specifically scoping your variables (:), that you are running into issues when trying to run in the 2 different environments.  The other item I observed is that your method is going to result in a very slow piece of code.  Given that, I have rewritten your script, using some methods that I believe will work in both environments, and will also run much faster.  The script below run against the Semiconductor Capability data table that is deistributed with JMP.  But if you just strip out the reference to opening that table, along with the statement following the Open of the data table and replace them with your

     dt = current data table();

the code should work on your data.

I have annotated the code below, so I think you should be able to follow it.  Also, I VERY STRONGLY SUGGEST that you read the Scripting Guide distributed with your install of JMP.

     Help==>Books==>Scripting Guide

It will tell you all about what those crazy colons are all about

Names Default To Here( 1 );

dt = Open( "$SAMPLE_DATA/semiconductor capability.jmp" );

dt << delete columns( {2, 3, 4} );

// Get the numeric column names as strings
colNames = dt << get column names( Numeric, string );

// Create the new calculated columns
// They will be populated later
dt << New Column( "Num Tests Failing", numeric );
dt << New Column( "Failing Tests", character );

// Create a new column for each column in the colNames list
// and populate the colNames name, if it does NOT pass
For( i = 1, i <= N Items( colNames ), i++,
	// Create the new colum
	dt << New Column( "PassFail " || Char( i ), character );
	// Get the Spec Limits for the first column
	specLimits = Column( dt, colNames[i] ) << get property( "Spec Limits" );
	// If no Spec Limits are found, do not process
	If( Is Empty( specLimits ) == 0,
		// Get the USL and LSL and if not present set to missing
		LSL = Try( specLimits["LSL"], . );
		USL = Try( specLimits["USL"], . );
		// If the LSL is not missing process
		If( Is Missing( LSL ) == 0,
			// Find all rows where the colNames column is less than
			// the LSL
			dt << select where( As Column( dt, colNames[i] ) < LSL );
			// If any rows are selected, then process
			If( N Rows( dt << get selected rows ) > 0,
				// Set the value in the PassFail column to the name of
				// the colNames being processed.  This will allow for 
				// the counting and the concatenating for the new
				// calculation columns
				Column( dt, "PassFail " || Char( i ) )[dt << get selected rows] = colNames[i]
				 || "; "
			);
		);
		// See above comments for LSL
		If( Is Missing( USL ) == 0,
			dt << select where( As Column( dt, colNames[i] ) > USL );
			If( N Rows( dt << get selected rows ) > 0,
				Column( dt, "PassFail " || Char( i ) )[dt << get selected rows] = colNames[i]
				 || "; "
			);
		);
	);
);

// Clean up and selected columns 
dt << clear select;

// Create the command string required for the formula for the
// Failing Tests, which is a concatenated string of all of the
// PassFail columns.  
theExpr = "dt:Failing Tests << set formula(Concat(:PassFail 1";
For( i = 2, i <= N Items( colNames ), i++,
	theExpr = theExpr || ",:PassFail " || Char( i )
);
theExpr = theExpr || "))";
// Execute the command string
Eval( Parse( theExpr ) );
// Delete the formula, which will remove the formula, but not
// the calculated values
dt:Failing Tests << delete formula;

// Create the command string for the Num Tests Failing column
// The count is the sum of the "True" Boolean comparisions.
// That is, if PassFail 1 != "", it will be "True" and therefore
// will have the value of 1.  If the cell is blank, it will have a 
// value of 0.  The sum of all of these comparisons will be equal to
// the number of non passing parameters(tests)
theExpr = "dt:Num Tests Failing << set formula(sum(:PassFail 1 != \!"\!"";
For( i = 2, i <= N Items( colNames ), i++,
	theExpr = theExpr || ",:PassFail " || Char( I ) || " != \!"\!""
);
theExpr = theExpr || "));";
Eval( Parse( theExpr ) );
dt:Num Tests Failing << delete formula;

// Delete all of the PassFail columns, just to clean up the data table
dt << delete columns(index(n cols(dt)-N items(colNames)+1,ncols(dt)));
Jim
gzmorgan0
Super User (Alumni)

Re: Script works in scripting editor but not on menu item (Get Column Names not behaving the same)

Jim,

 

Nice job.  I needed to add 

dt << Run Formulas;

after Eval(Parse(theExpr)) and prior to the delete formula statement, in two places of your nicely written script.  

tatarjj
Level I

Re: Script works in scripting editor but not on menu item (Get Column Names not behaving the same)

Thanks for the script Jim! You're right, I have no idea how variable scoping works in JMP script, except that I have seen some evidence to suggest that declared variables default to be local variables.  

 

How did you get your script to display correctly on these forums?  When I paste code into the code window on these forums, it screws up the formatting and deletes all the tabs and whitespace indentation.   Good thing we're not trying to share Python code! 

 

Anyway, I'm adapting my script to use elements from yours, and it already is working finally.  I tried using your script directly with dt= current data table(); and another edit or two, but it didn't work.  I know my script in inefficient, but for example, all my attempts to convert the USL and LSL into numbers failed, so I had to just convert it to a string and use a substring.  If I make the adjustment I note in the comments to extract all spec limits just once, then that won't matter too much though.  It's even useful to see an open file example too, because what I'd really like to do is have the option to pull test limits from different files than the one I'm analyzing!


What's odd to me is your implication (because you said your script was faster) that adding columns and deleting them would be more efficient than two nested For loops.  The computer still has to analyze each and every cell in your data table.  Is JMP optimized such that setting and running formulas is more efficient?   I can't get your approach to actually work to try to find out, maybe I should try what the follow-up poser suggests.

 

By the way, my data tables have something like 600 columns and can have tens of thousands of rows, so that might need to go into execution efficiency considerations.

 

Anyway, the minimum change I needed to make to make my script work was replace all the 

colNames[colInd] 

with

Column( dt, colNames[colInd] ) .

 

Even though I can't get it to work with my data files, I'll try adapting some more elements from your script.

txnelson
Super User

Re: Script works in scripting editor but not on menu item (Get Column Names not behaving the same)

1. To get code to display as JSL, you need to use the <JSL> tool at the top of the input window for inputting your text.

2. The main reason that I believe my code will work faster than your method, is based upon 2 items.

     a. Yes, formulas are optimized.

     b. Using the dt << Select Where coupled with being able to assign to all seelcted rows at one time is extremly fast.  Looping through a data table with all of the "IF" statements your code has is very slow.

3. Concerning your character spec limit values.  How are you assigning the Spec Limits?   The Spec Limits column property should only allow numeric values.  If you are accessing the Spec Limits list by using character functions like Substr(), you are going to get back character strings.  Take a look in the Scripting Guide on how to manipulate JSL Lists

Jim