Share your ideas for the JMP Scripting Unsession at Discovery Summit by September 17th. We hope to see you there!
Choose Language Hide Translation Bar
Highlighted
adjo9835
Level II

Would you implement any blocks of this script differently?

Hello.  I am new to jsl and have a feeling I am doing alot of things the long and hard way and that there may be built in functions which perform the same actions as my code.  The code I've written takes 2 data tables and converts them into 1 .mdf file  If you know of a simpler and/or more robust way of implementing any of the sections of code below, please clue me in! Sample data has been uploaded to this page.  Thank you.

//************************************************************************************************************************************************************************************************************************************
//
//	AUTHOR: Adam Jones			DATE:	7 July 2019
//
//	Edits:	15 July 2019, Adam Jones. Removed IdVd data from output. 
//			16 July 2019, Adam Jones. Added functionality of allowing user to leave data windows on screen after script is finished or to clear all windows after script has finished
//									  Added ability to handle input files containing differnt processes or device numbers.  Script saves output files as normal, but shows error window, leaving user to verify validity of output data
//			xx July 2019, Adam Jones. Added functionality of allowing user to set lambda for each capacitor and/or iv spline.
//
//	DESCRIPTION:	Converts CV and IV data from 2 independent .jmp or .csv files into 1 .mdf file, for use in ADS/AWR.
//
//	INPUTS:	CV data (.jmp or .csv, must contain columns: serial_num, v_dut_volt, c_type, process, reticle )
//		IV data	(.jmp or .csv, must contain columns: serial_num, vg_volt, , process, reticle )
//		Directory for output data
//
//	OUTPUTS:	3 .mdf files, containing CV, IV and CV+IV data, seperated into blocks, based on curve (Cdg-V, Cgg-V, Cds-V, Id-Vg), process and device number
//			1 .csv file, for use by an ADS batch simulatior (CSV_list Sweep Module)
//			WARNING: Output will overide any files in the output directory with the same name
//
//	FAIL CASES: 	1) Input file does not contain correct column names
//			2) Cell of columns named "process", "c_type", "sn", or "device_num" has been left blank
//			3) File contains IdVd data as well as IdVg data
//			4) Both input files contain different processes, sn's, device numbers
//			5) Incorrect user input
//
//************************************************************************************************************************************************************************************************************************************
//	User input
DataIVname = Pick File(	// Prompt user to choose a file and return a string of the filepath
	"Select DCIV Data", // prompt message
	"", // initial directory
	{"All Files|*", "Excel Files|csv", "JMP Files|jmp;jsl;jrn"}, // file filter list
	1, // intially selected item
	0, // doesn’t prompt the user to save the file
	"" // file that is selected by default
);
DataCV = Open(	// Open file and assign it to variable
	Pick File(	// Prompt user to choose a file and return a string of the filepath
		"Select CV Data", // prompt message
		"", // initial directory
		{"All Files|*", "Excel Files|csv", "JMP Files|jmp;jsl;jrn"}, // file filter list
		1, // intially selected item
		0, // doesn’t prompt the user to save the file
		"" // file that is selected by default
	)
);
prefilepath = Munger( Pick Directory( "Select Output Directory" ), 1, "/", "" );	// Prompt user to chose folder for data storage. Assign it to variable
filepath = Convert File Path( prefilepath, Windows );	// Convert prefilepath variable into a string, containing the filepath, in windows format 
New Window( "Would you like to visually inspect data and lines of best fit for garbage or corruption?",
	<< modal,
	Text Box ("Would you like to visually inspect data and lines of best fit for garbage or corruption?"),
	Button Box( "Yes" , inspect = 0 ), 
	Button Box( "No" , inspect = 1 )
);
//************************************************************************************************************************************************************************************************************************************
//	Generate and save CV splines (curves of best fit) by combinations of :c_type and :process 
Summarize( DataCV, processArrayCV = By( :process ) );	// Create array of manufacturing processes
Summarize( DataCV, deviceNumArrayCV = By( :device_num ) );
Summarize( DataCV, ctypeArray = By( :c_type ) );	// Create array of capacitance types
For( n = 1, n <= Length( ctypeArray ), n++, 	// Loop through ctypeArray
	For( o = 1, o <= Length( deviceNumArrayCV ), o++, 	// Loop through deviceNumArrayCV
		For( m = 1, m <= Length( processArrayCV ), m++, 	// Loop through processArrayCV
			temp = Substitute( 	// Return Expr(), after substitutions have taken place
					Expr(	// Define expression containing variables to be substituted
						biv = Bivariate(	// Generate line of best fit
							Y( :c_mm_deembed_pf ), 	// Set dependent variable 
							X( :v_dut_volt ), 	// Set independent variable
							Where( :c_type == __ctype__ & :process == __process__ & :device_num == __deviceNum__ ), 	// Genearte lines based on 2D array: __ctype__ by __process__
							Fit Spline( /*__lambda__*/ 0.01, standardized, {save predicteds} )	// Define type of fit (spline), define lambda, save predicted values
						)
					),
				Expr( __process__ ), processArrayCV[m], 	// Substitute value of processArrayCV into Bivariate()
				Expr( __deviceNum__ ), deviceNumArrayCV[o],
				Expr( __ctype__ ), ctypeArray[n], 	// Substitute value of ctypeArray into Bivariate()
/*Expr( __lambda__ ), if(ctypeArray[n] == "Cgg",	// If ctype is Cgg, set lambda to 0.001, othwerwise set to 10.  lambda = 10 reduces noise but lambda <= 0.01 is needed to capture discontinuities in Cgg curves
										0.001, 0.1
									);		*/		 
			);
			Eval( temp );
		)
	)
);

//************************************************************************************************************************************************************************************************************************************
//	Create new CV table, containing only useful columns
CVdata = (Data Table( DataCV ) << Tabulate(
	Add Table(
		Column Table( Analysis Columns( :Spline Predictor for c_mm_deembed_pf Where, /*:Spline Predictor for c_mm_deembed_pf Where 2*/ ) ), 	// Dependent variable goes here. 1 to 1 relationship with leaves of categorical tree
		Row Table( Grouping Columns( :serial_num, :process, :device_num, :c_type, :v_dut_volt ) )	// Defines categorical tree, growing left to right, from :serial_num (trunk) to :v_dut_volt (leaves, independent variable)
	)
)) << Make Into Data Table;

//************************************************************************************************************************************************************************************************************************************
// Throw out redundant CV data
CVdata << AddRows( 1 );	// Add empty last row so while loop knows when it's reached the end
q = 0;	// End of data table flag
proc = "new";	// Check against every row of :process
dn = "new";
sn = 0;
i = 1;	// Row number
Print( "flag1" );
while( q == 0, 				// Delete redundant :process x :c_type combinations
	If( proc != :process[i] | dn != :device_num[i], // (1) If new process or device number has been detected, reset proc, sn and dn
		proc = :process[i];
		sn = :serial_num[i];
		dn = :device_num[i];
		i += 1;
	);
	If( proc == :process[i] & sn == :serial_num[i] & dn == :device_num[i], 		// (2) Iterate through all rows of the new :process x :device_num combination
		i += 1
	);
	If( proc == :process[i] & dn == :device_num[i] & sn != :serial_num[i], 		// (3) If new sn has been encountered, with the same process and device num, delete row.  Keep deleting until new process or device num has been detected (1)
		CVdata << DeleteRows( i )
	);
	If( :process[i] == "", 		// (4) If end of table has been reached, exit loop
		q = 1
	)
);
CVdata << Delete Rows( i );	// Delete empty last row

//************************************************************************************************************************************************************************************************************************************
//	Add blocks, seperated by VARs, BEGINNINGs and ENDs, for use by ADS/AWR
Column( "v_dut_volt" ) << set name( "independent" );	// Change name of column "v_dut_volt" to "Independent"
Column( "independent" ) << data type( character );	// Change column type from numerical to character so that later on, VARs, used for ADS/AWR indexing, can be inserted into this column
Column( "Sum(Spline Predictor for c_mm_deembed_pf Where)" ) << set name( "spline" );	// change name of column "Sum(Spline Predictor for c_mm_deembed_pf Where)" to "Spline"
Column( "spline" ) << data type( character );		// Change column type from numerical to character so that later on, VARs, used for ADS/AWR indexing, can be inserted into this column
batchList = {};
j = 0;	// First block flag
cap = "new";	// Check against every row of :c_type
For( k = 1, k <= N Rows( CVdata ), k++, 	// Add VARs for ADS indexing
	If( cap != :c_type[k] & j == 1, 		// Enter this if loop every time except for first time, where "END" is not required
		CVdata << Add Rows( 4, k - 1 );	// Insert 6 new rows, above newly detected capacitor type
		cap = :c_type[k + 4]; 	// set variable to newly detected capacitor type
		proc = :process[k + 4]; 	// set variable to process of row containing new capacitor type
		dn = :device_num[k + 4];
		:independent[k] = "END";	// Mark end of a block
		:independent[k + 1] = "VAR Index(2) = " || Char( proc ) || "/" || Char( dn ) || "/" || Char( cap );	// Index of .mdf block, for use by ADS/AWR
		:independent[k + 2] = "BEGIN";	// Mark beginning of a block
		:independent[k + 3] = "% ind(1)"; // Lable first column as independent variable, for use by ADS/AWR
		:Spline[k + 3] = "dep(1)";	// Lable second column as dependent variable, for use by ADS/AWR
		k += 3	// Move k past newly created rows
		;
	);
	If( cap != :c_type[k] & j == 0, 	// Enter this if loop only once, at the beginning of the file, where "END"" is not required
		CVdata << Add Rows( 3, k - 1 ); // Insert 5 new rows, above newly detected capacitor type
		cap = :c_type[k + 3]; // Set variable to newld detected capacitor type
		proc = :process[k + 3];	// Set variable to process of newly detected capacitor
		dn = :device_num[k + 3];
		:independent[k] = "VAR Index(2) = " || Char( proc ) || "/" || Char( dn ) || "/" || Char( cap );	// Index of .mdf block, for use by ADS/AWR
		:independent[k + 1] = "BEGIN";	// Mark beginning of block
		:independent[k + 2] = "% ind(1)";	// Lable first column as independent variable, for use by ADS/AWR
		:Spline[k + 2] = "dep(1)";	// Lable second column as dependent variable, for use by ADS/AWR
		k += 2;	// Move k past newly created rows
		j = 1	// Set flag to 1, so END will be included at end of blocks
		;
	);
);
CVdata << add rows( {:independent = "END"} );	// Insert "END" at very end of file

//************************************************************************************************************************************************************************************************************************************
//	Clean and save CV data table.  Clean windows
CVdata << delete columns( "c_type", "serial_num", "process", "device_num" );	// Delete all rows except for ind(1) and dep(1)
CVdata << Save As( Filepath || "CV_Output.csv" ); // Save as .csv file for concatenation to IV file
if(inspect == 1,
	Close All( Data Tables, NoSave )	// Close pop-up windows
);

//************************************************************************************************************************************************************************************************************************************
// Generate and save IV splines (curves of best fit) by combinations of :process and :reticle_num
DataIV = Open( DataIVname );	// Open file containing IV data
Summarize( DataIV, processArrayIV = By( :process ) );	// Create array 
Summarize( DataIV, deviceNumArrayIV = By( :device_num ) );
Print( "flag2" );
For( m = 1, m <= Length( processArrayIV ), m++, 		// Generates and saves all IdVg splines
	For( n = 1, n <= Length( deviceNumArrayIV ), n++,
		temp = Substitute(
				Expr(
					biv = Bivariate(
						Y( :id_amp ),
						X( :vg_volt ),
						Where( :process == __process__ & :device_num == __deviceNum__ & :iv_type == "idvg" ),
						Fit Spline( 0.001, standardized, {save predicteds} )
					)
				),
			Expr( __process__ ), processArrayIV[m],
			Expr( __deviceNum__ ), deviceNumArrayIV[n]
		);
		Eval( temp );
	)
);

//************************************************************************************************************************************************************************************************************************************
//	Create new IV table, containing only useful columns
IVdata = (Data Table( DataIV ) << Tabulate(	// Create new table
	Add Table(
		Column Table( Analysis Columns( :Spline Predictor for id_amp Where ) ),
		Row Table( Grouping Columns( :serial_num, :process, :device_num, :vg_volt, :iv_type ) )
	)
)) << Make Into Data Table;

//************************************************************************************************************************************************************************************************************************************
// Throw out redundant IV data
IVdata << AddRows( 1 );	// Add an empty last row so while loop knows when it's at the end
q = 0;
dn = "new";
proc = "new";
sn = 0;
i = 1;
Print( "flag4" );
while( q == 0, 			// Delete redundant splines (:process x :reticle_num combinations)
	If( proc != :process[i] | dn != :device_num[i] & :iv_type[i] == "idvg",
		proc = :process[i];
		dn = :device_num[i];
		sn = :serial_num[i];
		i += 1;
	);
	If( proc == :process[i] & dn == :device_num[i] & sn == :serial_num[i] & :iv_type[i] == "idvg",
		i += 1
	);
	If( proc == :process[i] & dn == :device_num[i] & sn != :serial_num[i] | :iv_type[i] == "idvd",
		IVdata << DeleteRows( i )
	);
	If( :device_num[i] == "",
		q = 1
	);
);
IVdata << Delete Rows( i );	// Delete empty last row

//************************************************************************************************************************************************************************************************************************************
// Add blocks, seperated by VARs, BEGINNINGs and ENDs, for use by ADS/AWR
Column( "vg_volt" ) << set name( "independent" );
Column( "independent" ) << data type( character );
Column( "Sum(Spline Predictor for id_amp Where)" ) << set name( "spline" );
Column( "spline" ) << data type( character );
j = 0;
dn = "new";
For( k = 1, k <= N Rows( IVdata ), k++, 	// Add VARs for ADS indexing
	If( dn != :device_num[k] & j == 1,
		IVdata << Add Rows( 4, k - 1 );
		dn = :device_num[k + 4];
		proc = :process[k + 4];
		:independent[k] = "END";
		:independent[k + 1] = "VAR Index(2) = " || Char( proc ) || "/" || Char( dn ) || "/IV";	// Index of .mdf block, for use by ADS/AWR
		:independent[k + 2] = "BEGIN";
		:independent[k + 3] = "% ind(1)";
		:Spline[k + 3] = "dep(1)";
		k += 3;
	);
	If( dn != :device_num[k] & j == 0,
		IVdata << Add Rows( 3, k - 1 );
		dn = :device_num[k + 3];
		proc = :process[k + 3];
		:independent[k] = "VAR Index(2) = " || Char( proc ) || "/" || Char( dn ) || "/IV";	// Index of .mdf block, for use by ADS/AWR
		:independent[k + 1] = "BEGIN";
		:independent[k + 2] = "% ind(1)";
		:Spline[k + 2] = "dep(1)";
		k += 2;
		j = 1;
	);
);
IVdata << add rows( {:independent = "END"} );

//************************************************************************************************************************************************************************************************************************************
//	Clean and save IV data table
IVdata << delete columns( "device_num", "serial_num", "process", "iv_type" );	// Delete all rows except for ind(1) and dep(1)
IVdata << Save As( Filepath || "IV_Output.csv" );	// Save as .csv
if(inspect == 1,
	Close All( Data Tables, NoSave )	// Close pop-up windows
);

//************************************************************************************************************************************************************************************************************************************
// Concatinate IV and CV data
Fulltable = New Table( "Combined Data table" );	// Create new table
IVtable = Open( Filepath || "IV_Output.csv" );	// Open IV data
CVtable = Open( Filepath || "CV_Output.csv" );	// Open CV data
Fulltable << Concatenate( Data Table( IVtable ), append to first table );	// Concatenate IV data to empty table
Fulltable << Concatenate( Data Table( CVtable ), append to first table );	// Concatenate CV data to IV data
FullTable << delete columns( "Column 1" );	// Throw out garbage column

//************************************************************************************************************************************************************************************************************************************
//	Save concatinated data table in .MDF format
xptPref = Get Preferences( Export Settings );
Preferences(
	ExportSettings(	// .mdf format
		End Of Line( CRLF ), 	// End of line marked by carrage return left field
		End Of Field( Tab ), 	// Tab delineated 
		Export Table Headers( 0 )	// Do not include table headers
	)
);
Fulltable << Save as( Filepath || "CV-IV_Output.mdf", "text" );	// Save combined IV and CV data as a .mdf file

//************************************************************************************************************************************************************************************************************************************
//	Check that CV file contains exact same processes and device numbers as IV data
If( Length( processArrayCV ) != Length( processArrayIV ),
	New Window( "Error",
		<<Modal,
		Text Box( "IV and CV data contain differnt number of processes" ),
		Button Box( "OK" )
	)	
);
v = 0;
w = 0;
x = 0;
found = 0;
founds = {};
Show( processArrayCV );
Show( processArrayIV );
For( s = 1, s <= Length( processArrayCV ), s++,
	For( t = 1, t <= Length( processArrayIV ), t++,
		If( processArrayCV[s] == processArrayIV[t],
			found = 1;
			v++;
		)
	);
	If( found == 1,
		founds[s] = 1;
		w++,
		founds[s] = 0;
		x++;
	);
	found = 0;
);
For( u = 1, u <= Length( founds ), u++,
	If( founds[u] == 0,
		New Window( "Error",
			<<Modal,
			Text Box( "IV data's processes do not match CV data's processes" ),
			Button Box( "OK" )
		);	
		Break()
	)
);
If( Length( deviceNumArrayCV ) != Length( deviceNumArrayIV ),
	New Window( "Error",
		<<Modal,
		Text Box( "IV and CV data contain differnt number of device numbers" ),
		Button Box( "OK" )
	)	);
v = 0;
w = 0;
x = 0;
found = 0;
founds = {};
Show( deviceNumArrayCV );
Show( deviceNumArrayIV );
For( s = 1, s <= Length( deviceNumArrayCV ), s++,
	For( t = 1, t <= Length( deviceNumArrayIV ), t++,
		If( deviceNumArrayCV[s] == deviceNumArrayIV[t],
			found = 1;
			v++;
		)
	);
	If( found == 1,
		founds[s] = 1;
		w++,
		founds[s] = 0;
		x++;
	);
	found = 0;
);
For( u = 1, u <= Length( founds ), u++,
	Show( founds[u] );
	If( founds[u] == 0,
		New Window( "Error",
			<<Modal,
			Text Box( "IV data's device numbers do not match CV data's device numbers" ),
			Button Box( "OK" )
		);	
		Break();
	);
);

//************************************************************************************************************************************************************************************************************************************
//	Generate Batch List File
batchList = {};
For( q = 1, q <= Length( processArrayCV ), q++,
	For( r = 1, r <= Length( deviceNumArrayCV ), r++,
		Insert Into( batchList, processArrayCV[q] || "/" || deviceNumArrayCV[r] )
	)
);
For( s = 1, s <= Length( processArrayIV ), s++,
	For( t = 1, t <= Length( deviceNumArrayIV ), t++,
		Insert Into( batchList, processArrayIV[s] || "/" || deviceNumArrayIV[t] )
	)
);
Insert Into( batchList, "XXX" );
Show( BatchList );
v = 0;
q = 1;
while( 1, 	// remove redundant values from the batch file
	v += 1;
	If( batchList[v] == "XXX",
		Break()
	);
	while( 1,
		If( v == q, q += 1 );
		If( batchList[q] == "XXX",
			Break()
		);
		If( batchList[v] == batchList[q],
			batchList = Remove( batchList, q ),
			q += 1
		);
	);
	y = 0;
	q = 1;
);
batchList = Remove( batchList, v );	// Remove XXX from end of batch list
batchTable = New Table( "Batch List" );
batchTable << New Column( "Index2" );
Column( "Index2" ) << Data Type( Character );
For( p = 1, p <= Length( batchList ), p++,
	Print( batchList[p] );
	BatchTable << Add Rows( {:Index2 = batchList[p]} );
);
BatchTable << Save as( Filepath || "BatchList.csv" );

//************************************************************************************************************************************************************************************************************************************
//	Clean monitor
if(inspect == 1,
	Close All( Data Tables, NoSave )	// Close pop-up windows
);

 

 

1 REPLY 1
Highlighted
Craige_Hales
Staff (Retired)

Re: Would you implement any blocks of this script differently?

Looks like well commented code that won't be hard to maintain. I'm not really answering the question you asked because I don't see anything really wrong.

 

Superficial thoughts:

 

 

Close All( Data Tables, NoSave )

at the end might be a bit of a surprise to someone that had important modifications to an open data table. You can save the tables you open, perhaps in a list, and close just the ones you made.

 

 

When you set the prefs, you captured the original pref values but didn't use the captured value to restore them:

 

xptPref = Get Preferences( Export Settings );

I think you can just eval the expression in xptPref after the save to do the restore.

 

 

If you have more than one table open, you should use a table variable with the column names; this might be a big help to a future maintainer:

 

	If( proc != dtA:process[i] | dn != dtA:device_num[i], 

things get terribly confusing if the current table is used and more than one table has a process or device_num column. If you can use a better name than my dtA, that will help too.

 

 

The modal dialog for the inspect variable should handle the cancel case; if you "X" the dialog closed rather then Yes/No the inspect variable is undefined. You should set inspect to -1, perhaps, before the modal window. Also the 0/1 logic for inspect seems reversed for its name.

 

Thanks for sharing; I like the way you used the tab delimited/no header output of a "text" file to make the file you needed.

 

 

Craige
Article Labels

    There are no labels assigned to this post.