topic Re: How do I apply a script to multiple data tables at a time? (JMP 17) in Discussions

How do I apply a script to multiple data tables at a time? (JMP 17)

Kast — Fri, 09 Jun 2023 23:53:55 GMT

I'm looking for a way to execute a script across multiple data tables by executing the script once as opposed to executing the script one time per data table.

Current Data Flow

Open reference JMP data table.
Use a 1st script (based on Multiple File Import) to import multiple excel files into multiple JMP data tables (1 Table Per File).
1. Result: 1 Reference Data Table Open and 2-10+ new JMP Data Tables from import script.
Run a 2nd script on each created JMP DataTable to clean up the data and update the created data tables with data from the reference JMP data table.
1. Result: 1 Reference Data Table and 2-10+ JMP data tables that have been cleaned up (delete columns, add calculated columns, etc) and have been updated (scripted Update data table) with data from the reference data table.

I'd like to find a solution where I don't have to manually apply my 2nd script individually to each created JMP Data Table.

I'm open to any approaches.

I've thought about trying to use JMP lists and/or the "For Each" function but I haven't been able to find examples of applying these to data tables. I think I would need to make a list of all open data tables filtered by their name (so I can filter out the reference data table I use to update the data tables of interest), I also don't know how to do this.

Thank you for any and all advice and ideas.

Re: How do I apply a script to multiple data tables at a time? (JMP 17)

jthi — Fri, 09 Jun 2023 19:02:36 GMT

Using For Each is the correct idea. You get the reference to your reference data table when you open it and you get a list of references to other datatables from Multiple File Import (if I remember correctly...). You can then loop over that list by using For Each

Re: How do I apply a script to multiple data tables at a time? (JMP 17)

StarfruitBob — Fri, 09 Jun 2023 19:17:19 GMT

Hello @Kast,

A follow-up to what @jthi mentioned. A script to do this might look like this. The correct expression syntax is still a bit fuzzy to me, but this should be most of the way to what you need.

Names default to here(1);

dt_list = Multiple file import(); // Incomplete placeholder

table_script = expr( New script( "Table script name here", // Add new tables scripts with New script()
	Graph Builder( // Incomplete table script graphic script 
		Size( 800, 800 ),
		Show Control Panel( 0 ),
		Variables( X( :Column 1 ) ),
		Elements( Points( X, Y, Legend( 5 ) ) ),
		SendToReport(
			Dispatch(
				{},
				"graph title",
				TextEditBox,
				{Set Text( "Example title" )}
			)
		)
	)
);

Eval( // For each loop adds table_script to each data table named in dt_list
	Eval Expr(
		For Each( {value, index},
			Expr( dt_list[index] << table_script )
		)
	)
);

Eval( // for loop alternative
	eval expr(
		for( i = 1, i <= N Items( dt_list ), i++, 
			expr( dt_list[i] << table_script )	
		)
	)
);

Re: How do I apply a script to multiple data tables at a time? (JMP 17)

Kast — Tue, 13 Jun 2023 21:41:52 GMT

Thank you both. Really appreciate the help.

dt_list = Multiple File Import(); //with all Multiple File Import details included

definitely works for making the list.

I wasn't able to get this to work for myself, but I'm still learning how to use Eval, EvalExpr, Expr, etc.

Eval( // For each loop adds table_script to each data table named in dt_list
	Eval Expr(
		For Each( {value, index},
			Expr( dt_list[index] << table_script )
		)
	)
);

I was able to get a simple test to work with For Each in this format. Simple added column script.

For Each({listitem}, dt_list, listitem << New Column( "Mean",
	Numeric,
	"Continuous",
	Formula( Mean( :A, :B, :C, :D ) ),
	Format( "Best", 12 )));

My complete working example code is

Names default to here(1);

dt_list = Multiple File Import(
	<<Set Folder(Get Default Directory();),
	<<Set Show Hidden( 0 ),
	<<Set Subfolders( 0 ),
	<<Set Name Filter( "*.xlsx; " ),
	<<Set Name Enable( 1 ),
	<<Set Size Enable( 0 ),
	<<Set Date Enable( 0 ),
	<<Set Add File Name Column( 1 ),
	<<Set Add File Size Column( 0 ),
	<<Set Add File Date Column( 0 ),
	<<Set Import Mode( "CSVData" ),
	<<Set Charset( "Best Guess" ),
	<<Set Stack Mode( "Table Per File" ),
	<<Set CSV Has Headers( 1 ),
	<<Set CSV Allow Numeric( 1 ),
	<<Set CSV First Header Line( 1 ),
	<<Set CSV Number Of Header Lines( 1 ),
	<<Set CSV First Data Line( 2 ),
	<<Set CSV EOF Comma( 1 ),
	<<Set CSV EOF Tab( 0 ),
	<<Set CSV EOF Space( 0 ),
	<<Set CSV EOF Spaces( 0 ),
	<<Set CSV EOF Other( "" ),
	<<Set CSV EOL CRLF( 1 ),
	<<Set CSV EOL CR( 1 ),
	<<Set CSV EOL LF( 1 ),
	<<Set CSV EOL Semicolon( 0 ),
	<<Set CSV EOL Other( "" ),
	<<Set CSV Quote( "\!"" ),
	<<Set CSV Escape( "" ),
	<<Set XML Method( "guess" ),
	<<Set XML Guess( "huge" ),
	<<Set XML Settings( XML Settings() ),
	<<Set JSON Method( "guess" ),
	<<Set JSON Guess( "huge" ),
	<<Set JSON Settings( JSON Settings() ),
	<<Set PDF Method( "guess" ),
	<<Set PDF Settings( PDF All Tables( Combine( All ) ) ),
	<<Set Import Callback( Empty() )
) << Import Data;

For Each({listitem}, dt_list, listitem << New Column( "Mean",
	Numeric,
	"Continuous",
	Formula( Mean( :A, :B, :C, :D ) ),
	Format( "Best", 12 )));