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
mdawson69
Level IV

How do I dynamically concatenate n data tables in JSL

I am creating a script to automate the workflow for a co-worker that is a novice JMP user. The the first operation of the script is to combine all of the iterations of data from model runs. This is accomplished by,

 

  1. The user is prompted to select the directory of the individual data tables that represent n iterations of a model run.
  2. The script opens all the iteration data tables that have a predefined name followed by the iteration number: “SGR Scenaio Iteration i”.
  3. As each iteration data table is opened, an Iteration column is added and prepopulated with the iteration number.
  4. After all the data tables have been opened, data tables 2 thru 10 are concatenated to the first data table, to create a new combined data table named “SGR Scenario Combined Iterations”.
  5. The original iterations are then closed without being saved; this is intentional to preserve the original data tables.

Here is the code for the first part of the script that accomplishes the tasks listed above:

 

Names Default to Here(1);
Delete Symbols();

//----------------------------------------------------------------------------------------------
// Select file path, then import and combine all iteration data tables into a single data table 
// of replicates of sorties completed across iterations.
//----------------------------------------------------------------------------------------------
path = Pick Directory("Select the directory containing the iteration files.");

iterationFile = path || "SGR Scenario Iteration 1.jmp";
dtIterationTables = Open(iterationFile, "Invisible");

// add Iteration column
dtIterationTables << New Column(
	"Iteration",
	Ordinal,
	Set Each Value(1)
);

dtIterationTables << Move Selected Columns(
	{"Iteration"},
	After(:Time of Day)
);

// get remaining iteration tables
i = 2;
iterationFile = path || "SGR Scenario Iteration 2.jmp";
While(File Exists(iterationFile),
	nTables = i;
	dtIterationFile = Open(iterationFile, "Invisible");
	
	// add Iteration column
	dtIterationFile << New Column(
		"Iteration",
		Ordinal,
		Set Each Value(i)
	);

	dtIterationFile << Move Selected Columns(
		{"Iteration"},
		After(:Time of Day)
	);
	
	//look for next iteration table
	i++;
	iterationFile = path || "SGR Scenario Iteration " || Char(i) || ".jmp";
);

// concatenate current interation table to first iteration table
// NOTE: AWAITING RESPONSE ON HOW TO MAKE THIS PROCESS DYNAMIC
Data Table("SGR Scenario Iteration 1") << Concatenate(
	Data Table("SGR Scenario Iteration 2"),
	Data Table("SGR Scenario Iteration 3"),
	Data Table("SGR Scenario Iteration 4"),
	Data Table("SGR Scenario Iteration 5"),
	Data Table("SGR Scenario Iteration 6"),
	Data Table("SGR Scenario Iteration 7"),
	Data Table("SGR Scenario Iteration 8"),
	Data Table("SGR Scenario Iteration 9"),
	Data Table("SGR Scenario Iteration 10"),
	Output Table("SGR Scenario Combined Iterations")
);

// close original iteration tables
i = 1;
While(i <= nTables,
	dtIterationTable = Data Table("SGR Scenario Iteration " || Char(i));
	Close(dtIterationTable);
	Clear Symbols(dtIterationTable);
	i++;
);

The problem I have is with Step 4. The current script is hard-coded to concatenate 10 data tables, but I need to be able to concatenate n data tables so that my co-worker is not locked into only processing 10 iterations, or worse, having to attempt to edit the JSL code each time the size of the data set changes (read: comes to me to change the script). You can see in the code that I have a variable, nTables, that is the count of the number of data tables that were opened, as every other code block is set up to handle n data tables.

 

Any assistance on this matter would be greatly appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: How do I dynamically concatenate n data tables in JSL

Take a look at the below code.  I should get you want you want.  I could not really test the code, but I believe it is pretty accurate.  It takes the approach of not having to keep track of how many data tables have been read in, since it does the concatenation after each file is input.

Names Default to Here(1);
Delete Symbols();

//----------------------------------------------------------------------------------------------
// Select file path, then import and combine all iteration data tables into a single data table 
// of replicates of sorties completed across iterations.
//----------------------------------------------------------------------------------------------
path = Pick Directory("Select the directory containing the iteration files.");

iterationFile = path || "SGR Scenario Iteration 1.jmp";
dtBaseIterationTables = Open(iterationFile, "Invisible");

dtBaseIterationTable << set name( "SGR Scenario Combined Iterations" );

// add Iteration column
dtBaseIterationTables << New Column(
	"Iteration",
	Ordinal,
	Set Each Value(1)
);

dtBaseIterationTables << Move Selected Columns(
	{"Iteration"},
	After(:Time of Day)
);

// get remaining iteration tables
i = 2;
//iterationFile = path || "SGR Scenario Iteration 2.jmp";
While(File Exists(path || "SGR Scenario Iteration " || char( i ) || ".jmp";),
	//nTables = i;
	dtIterationFile = Open(path || "SGR Scenario Iteration " || char( i ) || ".jmp", "Invisible");
	
	// add Iteration column
	dtIterationFile << New Column(
		"Iteration",
		Ordinal,
		Set Each Value(i)
	);

	/* // Not required since the columns from this table will align with the base table
	dtIterationFile << Move Selected Columns(
		{"Iteration"},
		After(:Time of Day)
	)*/;
	
	//look for next iteration table
	i++;
	// iterationFile = path || "SGR Scenario Iteration " || Char(i) || ".jmp"; 
	
	dtBaseIterationTables = dtBaseIterationTables << Concatenate( dtIterationFile, append to first table( 1 ) );
	
	close( dtIterationFile, nosave );
);

dtBaseIterationTables << Show Window( 1 ); /*// concatenate current interation table to first iteration table // NOTE: AWAITING RESPONSE ON HOW TO MAKE THIS PROCESS DYNAMIC Data Table("SGR Scenario Iteration 1") << Concatenate( Data Table("SGR Scenario Iteration 2"), Data Table("SGR Scenario Iteration 3"), Data Table("SGR Scenario Iteration 4"), Data Table("SGR Scenario Iteration 5"), Data Table("SGR Scenario Iteration 6"), Data Table("SGR Scenario Iteration 7"), Data Table("SGR Scenario Iteration 8"), Data Table("SGR Scenario Iteration 9"), Data Table("SGR Scenario Iteration 10"), Output Table("SGR Scenario Combined Iterations") ); // close original iteration tables i = 1; While(i <= nTables, dtIterationTable = Data Table("SGR Scenario Iteration " || Char(i)); Close(dtIterationTable); Clear Symbols(dtIterationTable); i++; );*/
Jim

View solution in original post

2 REPLIES 2
txnelson
Super User

Re: How do I dynamically concatenate n data tables in JSL

Take a look at the below code.  I should get you want you want.  I could not really test the code, but I believe it is pretty accurate.  It takes the approach of not having to keep track of how many data tables have been read in, since it does the concatenation after each file is input.

Names Default to Here(1);
Delete Symbols();

//----------------------------------------------------------------------------------------------
// Select file path, then import and combine all iteration data tables into a single data table 
// of replicates of sorties completed across iterations.
//----------------------------------------------------------------------------------------------
path = Pick Directory("Select the directory containing the iteration files.");

iterationFile = path || "SGR Scenario Iteration 1.jmp";
dtBaseIterationTables = Open(iterationFile, "Invisible");

dtBaseIterationTable << set name( "SGR Scenario Combined Iterations" );

// add Iteration column
dtBaseIterationTables << New Column(
	"Iteration",
	Ordinal,
	Set Each Value(1)
);

dtBaseIterationTables << Move Selected Columns(
	{"Iteration"},
	After(:Time of Day)
);

// get remaining iteration tables
i = 2;
//iterationFile = path || "SGR Scenario Iteration 2.jmp";
While(File Exists(path || "SGR Scenario Iteration " || char( i ) || ".jmp";),
	//nTables = i;
	dtIterationFile = Open(path || "SGR Scenario Iteration " || char( i ) || ".jmp", "Invisible");
	
	// add Iteration column
	dtIterationFile << New Column(
		"Iteration",
		Ordinal,
		Set Each Value(i)
	);

	/* // Not required since the columns from this table will align with the base table
	dtIterationFile << Move Selected Columns(
		{"Iteration"},
		After(:Time of Day)
	)*/;
	
	//look for next iteration table
	i++;
	// iterationFile = path || "SGR Scenario Iteration " || Char(i) || ".jmp"; 
	
	dtBaseIterationTables = dtBaseIterationTables << Concatenate( dtIterationFile, append to first table( 1 ) );
	
	close( dtIterationFile, nosave );
);

dtBaseIterationTables << Show Window( 1 ); /*// concatenate current interation table to first iteration table // NOTE: AWAITING RESPONSE ON HOW TO MAKE THIS PROCESS DYNAMIC Data Table("SGR Scenario Iteration 1") << Concatenate( Data Table("SGR Scenario Iteration 2"), Data Table("SGR Scenario Iteration 3"), Data Table("SGR Scenario Iteration 4"), Data Table("SGR Scenario Iteration 5"), Data Table("SGR Scenario Iteration 6"), Data Table("SGR Scenario Iteration 7"), Data Table("SGR Scenario Iteration 8"), Data Table("SGR Scenario Iteration 9"), Data Table("SGR Scenario Iteration 10"), Output Table("SGR Scenario Combined Iterations") ); // close original iteration tables i = 1; While(i <= nTables, dtIterationTable = Data Table("SGR Scenario Iteration " || Char(i)); Close(dtIterationTable); Clear Symbols(dtIterationTable); i++; );*/
Jim
mdawson69
Level IV

Re: How do I dynamically concatenate n data tables in JSL

I had to do a little bit of cleanup on your suggested solution, but it bascially worked. Here is the updated code for the front end of my script:

 

Names Default to Here(1);
Delete Symbols();

//----------------------------------------------------------------------------------------------
// Select file path, then import and combine all iteration data tables into a single data table 
// of replicates of sorties completed by te hour across iterations.
//----------------------------------------------------------------------------------------------
path = Pick Directory("Select the directory containing the iteration files.");

iterationFile = path || "SGR Scenario Iteration 1.jmp";
dtIterationTables = Open(iterationFile, "Invisible");
dtIterationTables << Set Name("SGR Scenario Combined Iterations");

// add Iteration column
dtIterationTables << New Column(
	"Iteration",
	Ordinal,
	Set Each Value(1)
);

dtIterationTables << Move Selected Columns(
	{"Iteration"},
	After(:Time of Day)
);

// get remaining iteration tables
i = 2;
iterationFile = path || "SGR Scenario Iteration " || Char(i) || ".jmp";
While(File Exists(iterationFile),
	dtIterationFile = Open(iterationFile, "Invisible");
	
	// add Iteration column
	dtIterationFile << New Column(
		"Iteration",
		Ordinal,
		Set Each Value(i)
	);
	
	// append current iteration table to combined table
	dtIterationTables = dtIterationTables << Concatenate(
		dtIterationFile,
		Append to first table(1)
	);
	Close(dtIterationFile, NoSave);
	
	//look for next iteration data table
	i++;
	iterationFile = path || "SGR Scenario Iteration " || Char(i) || ".jmp";
);

//----------------------------------------------------------------------------------------------
// Add Replicate column and then create data table with simplified statistical model.
//----------------------------------------------------------------------------------------------
dtIterationTables << New Column(
	"Replicate",
	Ordinal,
	Formula(Num(Right(:Time of Day, Length(:Time of Day) - Contains(:Time of Day, " "))))
);

dtIterationTables << Move Selected Columns(
	{"Replicate"},
	After(:Iteration)
);

// make data set more flexible by stacking the hour columns to simplify the statistical model
dtIterationTables << Stack(
	columns(
		:Name("1200"),
		:Name("1300"),
		:Name("1400"),
		:Name("1500"),
		:Name("1600"),
		:Name("1700"),
		:Name("1800"),
		:Name("1900"),
		:Name("2000"),
		:Name("2100"),
		:Name("2200")
	),
	Source Label Column("Hour"),
	Stacked Data Column("Sorties Completed"),
	Copy formula(0),
	Name("Non-stacked columns")(Keep(:Iteration, :Replicate)),
	Output Table("Combined SGR Scenario Iterations")
);

dtAllIterations = Data Table("Combined SGR Scenario Iterations");
Close(dtIterationTables, NoSave);

dtAllIterations:Hour << Set Modeling Type("Ordinal");
dtAllIterations << Save As(path || "Combined SGR Scenario Iterations.jmp");

Thanks for the help!