topic Re: Close all data tables except a variable name list in Discussions

Close all data tables except a variable name list

WendyLou315 — Thu, 08 Aug 2019 15:06:40 GMT

Today I am trying to close all but a select number of data tables as a repeating procedure is run using Data Table Name variables. I have three tables. One where results are collected, one where invalid results of the first table are collected, and one that the user opens for the script to know what ID values to use to compare. I found a Discussion where tables can be closed (it has been included in my script here), but it uses quoted text in a list for the table names. Since my users will be choosing a different data table for each iteration for the third table, how can I use a data table name variable to include it in the list and leave it open at the end of processing?

DontClose = {CompareTable, CollectionTable, IDTable};   // Example names of tables you want to leave open
//CompareTable = "Validation Table"
//Collection Table = "Unmatched Values"
//IDTable = could be anything but I want to leave this open once the processing loop is completed

// Loop backwards through the list, so the table numbers do not change
For( i = N Table(), i > 0, i--,
	print( Data Table(i) << Get Name ) ;
	If( Contains( DontClose, Data Table( i ) << get name ),
		Continue(),
		Close( Data Table( i ), "No Save" )
	)
);

Re: Close all data tables except a variable name list

guillaumebugnon — Fri, 09 Aug 2019 13:28:05 GMT

Hello,

You can also set one table variable on each jmp datatable :

dt << Set Table Variable("ToClose","N");

Then you can loop and check the value :

For( i = N Table(), i > 0, i--,
  vtoclose = Data Table(i) << Get Table Variable ("ToClose") ;
  If ( Contains(vtoclose,"Y") >=1 ,
	   Close(Data Table(i),NoSave),
	   print("ToClose is not set to Y : do not close")
  );
);

Re: Close all data tables except a variable name list

Wendy_Murphrey — Fri, 09 Aug 2019 20:03:51 GMT

Here's an approach that allows you to use the data table references:

Names Default To Here( 1 );
dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
dt2 = Open( "$SAMPLE_DATA/Fitness.jmp" );
dt3 = Open( Pick File( "Select File", "$DOCUMENTS" ) );

dontClose = Eval List( {dt, dt2, dt3} );
Show( dontClose );

// Loop backwards through the list, so the table numbers do not change
For( i = N Table(), i > 0, i--,
	If( Contains( DontClose, Data Table( i ) ),
		Continue(),
		Close( Data Table( i ), "No Save" )
	)
);

Hope that helps!

Re: Close all data tables except a variable name list

WendyLou315 — Mon, 12 Aug 2019 13:51:23 GMT

@guillaumebugnon I see that ALL tables would need that variable set. The script I am writing opens and generates a multitude of tables so this would add a step that I feel is best left for less intense purposes. What I ultimately wish to do is have a list of IDs (could be over 200) and have the script run a process to produce at least 75 summarizations for each ID. Although this will work, it seems Wendy Murphrey's solution is just right for my application.

Thank you for taking the time to help with my dilemma!

Re: Close all data tables except a variable name list

WendyLou315 — Mon, 12 Aug 2019 13:53:29 GMT

@Wendy_Murphrey This is the ticket! Works just like I need. Since this script can open or produce hundreds of tables, this is perfect for my needs.

Thank you, thank you, thank you! I learn something new every day in JSL!

Re: Close all data tables except a variable name list

WendyLou315 — Mon, 12 Aug 2019 14:13:30 GMT

Oh my! I spoke too soon. I tested with opening tables and closing using this script.

What my script does is opens two tables that it needs to leave open for the full running of the script. One is the target for comparisons not being equal and the other houses a list of ID (which a user selects). Then it pulls data from servers, runs a plethora of summaries, transpositions, and other data manipulations, copies the results into the table where the script resides and differences into the target table (both need to be left open), then close all the summary tables and tables generated from the servers before going on to the next ID.

I keep getting an error like this:
Cannot locate data table{28} in access or evaluation of 'Data Table' , Data Table/*###*/(i)
because the table number is being lost?

Re: Close all data tables except a variable name list

Wendy_Murphrey — Mon, 12 Aug 2019 14:46:06 GMT

Can you post your script?
Closing tables that have linked Summary tables will automatically close the Summary table.

Re: Close all data tables except a variable name list

Mark_Bailey — Tue, 13 Aug 2019 11:23:13 GMT

Since you generate all of the data tables to be destroyed, why not insert each table reference into a list as it is created? You can then iterate over the list when it is time to close them.

Re: Close all data tables except a variable name list

WendyLou315 — Tue, 13 Aug 2019 17:26:10 GMT

I cannot post the entire script. It access proprietary servers and it would take me a large amount of time to create an anonymous version. But you have given me a thought that I was not accounting for that and that's why it's getting hung up. I will test my theory and report back!

THANK YOU!

Re: Close all data tables except a variable name list

WendyLou315 — Tue, 13 Aug 2019 17:36:17 GMT

That could work, but that's close to generating a Close() statement for each table after its use is completed. I was trying to use something a bit more elegant that uses fewer lines. The other similar option I have tried is to put all table names in a list and have a Keep List to compare against, the problem is one of the three tables that must stay open is selected by the user so changes from use to use. This is the only reason for me to use alias table names. That and as soon as I share this with someone, they will change the table name where the script resides so I'd prefer to not hard code data table names where I don't need to. It's quite the puzzle! Thanks for your reply!