nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Open 25 tables, Summary for each, and Concatenate all summary table.

Zihao

Hi,

I am a new JMP user and trying to do some auto data manipulation with JSL. As the title described, I need to

For (i=1, i <=Nitems ("directory"),i++,

1) Open one of the 25 tables;

2) Generate a summary table of the opened table;

);

3) After looping through all 25 tables, generate a concatenated table of 25 summary table.

I have done the 1st and 2nd part and looped them. But not sure how to automatically concatenate the resulted 25 summary tables. Now I concatenating them manually after all tables are generated.

The lack of the knowledge is how to name each summary table and refer to them when need to concatenate them.

Also, it would be good if the script can close all opened tables (from part 1)) and summary tables (from part 2)), only return the concatenated table. (With my script, it would pop out 50 tables in total, and I have to manually close them.)

Attached my scripts:

Set Default Directory( "E:\Test" );
files = files in directory ("E:\Test");
For(i = 1, i <=N Items(files), i++,
Open(
files[i],
Worksheets( "worksheet1" ),
Use for all sheets( 1 ),
Concatenate Worksheets( 0 ),
Create Concatenation Column( 0 ),
Worksheet Settings(
1,
Has Column Headers( 1 ),
Number of Rows in Headers( 1 ),
Headers Start on Row( 1 ),
Data Starts on Row( 2 ),
Data Starts on Column( 1 ),
Data Ends on Row( 0 ),
Data Ends on Column( 0 ),
Replicated Spanned Rows( 1 ),
Replicated Spanned Headers( 0 ),
Suppress Hidden Rows( 1 ),
Suppress Hidden Columns( 1 ),
Suppress Empty Columns( 1 ),
Treat as Hierarchy( 0 ),
Multiple Series Stack( 0 ),
Import Cell Colors( 0 ),
Limit Column Detect( 0 ),
Column Separator String( "-" )
)
);
New Column ("Xindex", Formula(Ceiling(:Name("X")/:Name("dimensionX"))));
New Column ("Yindex", Formula(Floor(:Name("Y")/:Name("dimensionY"))));

Summary(
Group(
:Name( "X" ),
:Name( "Y" ),
:Xindex,
:Yindex ),
Std Dev( :Name( "Data1" ) ),
Freq( "None" ),
Weight( "None" )
)

);

Accepted answers

txnelson

You just need to add a concatenation step to your code......something like this

Set Default Directory( "E:\Test" );
files = Files In Directory( "E:\Test" );
For( i = 1, i <= N Items( files ), i++,
	dt = Open(
		files[i],
		Worksheets( "worksheet1" ),
		Use for all sheets( 1 ),
		Concatenate Worksheets( 0 ),
		Create Concatenation Column( 0 ),
		Worksheet Settings(
			1,
			Has Column Headers( 1 ),
			Number of Rows in Headers( 1 ),
			Headers Start on Row( 1 ),
			Data Starts on Row( 2 ),
			Data Starts on Column( 1 ),
			Data Ends on Row( 0 ),
			Data Ends on Column( 0 ),
			Replicated Spanned Rows( 1 ),
			Replicated Spanned Headers( 0 ),
			Suppress Hidden Rows( 1 ),
			Suppress Hidden Columns( 1 ),
			Suppress Empty Columns( 1 ),
			Treat as Hierarchy( 0 ),
			Multiple Series Stack( 0 ),
			Import Cell Colors( 0 ),
			Limit Column Detect( 0 ),
			Column Separator String( "-" )
		)
	);
	New Column( "Xindex", Formula( Ceiling( :Name( "X" ) / :Name( "dimensionX" ) ) ) );
	New Column( "Yindex", Formula( Floor( :Name( "Y" ) / :Name( "dimensionY" ) ) ) );

	dtSum = dt << Summary(
		Group( :Name( "X" ), :Name( "Y" ), :Xindex, :Yindex ),
		Std Dev( :Name( "Data1" ) ),
		Freq( "None" ),
		Weight( "None" );
		
		If( i == 1,
			dtConcat = dtSum,
			dtConcat = dtConcat << concatenate( dtSum, append to first table );
			Close( dtSum, nosave );
		);
	);
);

txnelson

In the code, you specify

dtConcat = dtSum;

What I believe your thought process is, is that you are making a copy of the data table when you use this statement. But infact, dtConcat and dtSum are just variables, that contain a pointer to a data table, and by specifying dtConcat to be equal to dtSum, both variables are pointing to the same data table.

And then you specify

close (dtSum,nosave);

This closes the data table that the variable dtSum is pointing to. Which means that now both dtSum and dtConcat no longer have an open data table they are pointing to.

All comments

uday_guntupalli

@Zihao,
When you open a data table in JMP, one way to capture a reference to it is by doing something like this:

dt = Open(<your custom path/script>);

Now, dt holds the reference to the data table that you can pass messages and issue commands to. Now another way to pass messages to a data table is if you know the name of the data table

Data Table("Big Class") // This is an example

AS for your question on how to concatenate the data tables you could use the following template:

for(i = 1, i <= N Items(files), i++,
           dt = Open(<Custom Script Here>); 
          If(i == 1,
                dt_Results = New Table("Results");
                dt_Results = dt; 
                , 
                dt_Results << Concatenate(dt,"Append To First Table"); 
            ); 
    );

txnelson

You just need to add a concatenation step to your code......something like this

Set Default Directory( "E:\Test" );
files = Files In Directory( "E:\Test" );
For( i = 1, i <= N Items( files ), i++,
	dt = Open(
		files[i],
		Worksheets( "worksheet1" ),
		Use for all sheets( 1 ),
		Concatenate Worksheets( 0 ),
		Create Concatenation Column( 0 ),
		Worksheet Settings(
			1,
			Has Column Headers( 1 ),
			Number of Rows in Headers( 1 ),
			Headers Start on Row( 1 ),
			Data Starts on Row( 2 ),
			Data Starts on Column( 1 ),
			Data Ends on Row( 0 ),
			Data Ends on Column( 0 ),
			Replicated Spanned Rows( 1 ),
			Replicated Spanned Headers( 0 ),
			Suppress Hidden Rows( 1 ),
			Suppress Hidden Columns( 1 ),
			Suppress Empty Columns( 1 ),
			Treat as Hierarchy( 0 ),
			Multiple Series Stack( 0 ),
			Import Cell Colors( 0 ),
			Limit Column Detect( 0 ),
			Column Separator String( "-" )
		)
	);
	New Column( "Xindex", Formula( Ceiling( :Name( "X" ) / :Name( "dimensionX" ) ) ) );
	New Column( "Yindex", Formula( Floor( :Name( "Y" ) / :Name( "dimensionY" ) ) ) );

	dtSum = dt << Summary(
		Group( :Name( "X" ), :Name( "Y" ), :Xindex, :Yindex ),
		Std Dev( :Name( "Data1" ) ),
		Freq( "None" ),
		Weight( "None" );
		
		If( i == 1,
			dtConcat = dtSum,
			dtConcat = dtConcat << concatenate( dtSum, append to first table );
			Close( dtSum, nosave );
		);
	);
);

Zihao

Hi Jim,

Thanks a lot! Now I got some sense of using references in JMP.

One more question for if() function. In the following if() function I added in my script, i == 1 is test 1, dtConcat = dtSum is expr1, and the rest "dtConcat = dtConcat << ......." is "else expr".

My question is how can I add multiple expressions for test 1 in if()? In the first script, now I have three tables outputted,

1) dt when i ==1;

2)dtSum when i == 1;

3)dtConcat when the for() loop is done, which is exactly what I need.

Now I just want to not report 1) and 2) after running the script, only report 3).

I tried the second script but it doesn't work. Also, both scripts give alerts "Cannot append to a locked or derived data table."

if(i == 1,
dtConcat = dtSum,
dtConcat = dtConcat << Concatenate(dtSum,"Append to first table");close(dtSum,nosave);Close(dt,nosave)
);

In the second script, I tried to 1) when i == 1, assign dtSum to dtConcat, and close dtSum and dt; 2)when i is not equal to 1, Concat dtSum to dtConcat, and close dtSum and dt. However, it doesn't work as I expected. I guess that is because I am using comma and semicolon in a wrong way?

if(i == 1,
dtConcat = dtSum; close (dtSum,nosave); close (dt, nosave),
dtConcat = dtConcat << Concatenate(dtSum,"Append to first table");close(dtSum,nosave);Close(dt,nosave)
);

Zihao

Hi Uday,

Thanks a lot! I think your script tells me how to use semicolon and comma in if(): comma is used to separate test and expr, semicolon is used to join multiple arguments into one expr. Is this correct?

@txnelson

Hi Jim,

If I am correct about the comma and semicolon usage, I expect the following script should work by adding the highlighted line.

However, with this new script, I only got dt and dtSum when i = 2(I added another column in all tables to track what "i" it is in current table).

Without the highlighted line, I have dt and dtSum when i =1, and dtConcat when for() loop is done, just like I described in my last reply to you.

if(i == 1,
dtConcat = dtSum<FONT color="#FF6600"><STRONG>; close(dtSum,nosave);close(dt,nosave);</STRONG></FONT>,
dtConcat = dtConcat << Concatenate(dtSum,"Append to first table");close(dtSum,nosave);Close(dt,nosave);
);

txnelson

In the code, you specify

dtConcat = dtSum;

And then you specify

close (dtSum,nosave);

This closes the data table that the variable dtSum is pointing to. Which means that now both dtSum and dtConcat no longer have an open data table they are pointing to.