topic Re: concatenate data tables on the go in Discussions

concatenate data tables on the go

vishwasanj — Thu, 14 Sep 2017 21:43:32 GMT

Hi All,

This may be starighfroward but I am not able to get the right JSL commands.

I really appreciate if anyone could help me.

I am running a FOR loop and every time I run it, I open a data table.

I want to concatenate all data tables together and save it as FINAL TABLE.

Thanks.

Re: concatenate data tables on the go

uday_guntupalli — Thu, 14 Sep 2017 22:34:38 GMT

@vishwasanj :
Hello . I think this can be done through the following piece of code .

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

n = 5 ; // Random # of iterations 

for (i = 1 , i <= n , i++, 
	  RowsToSelect = Random Index(40,5);
	  dt << Select Rows(RowsToSelect); 
	  dt1 = dt << Subset("");  
	  
	  If(i == 1,
			dt_Results = dt1 ; 
			, 
			dt_Results << Concatenate(dt1,"Append To First Table");
			Close(dt1,"No Save");
	    );
	  
    );

Re: concatenate data tables on the go

uday_guntupalli — Thu, 14 Sep 2017 22:48:02 GMT

Just to explain what is going on here :

- Once you open the loop - we are randomly choosing which rows to select to generate a sample set - here

RowsToSelect = Random Index(40,5);
dt << Select Rows(RowsToSelect); 
dt1 = dt << Subset("");

- At i = 1 , you are initializing the format of the output data table that defines your results

- When i != 1 , you are concatenating data from the newly formed table back to the template file

Best

uday

Re: concatenate data tables on the go

vishwasanj — Thu, 14 Sep 2017 23:01:52 GMT

Thank you Uday. I think I get an idea of how to do it. But what I have is, I run the loop and it creates a new data table every time it completes. I don't have a control on the count or choice of the file, because it is created automatically. I could have 2, or 5, or 9 data tables at the end of the loop opened. I wanted to concatenate all into one table.

I hope I didn't over-complicate it. Let me know your thoughts. I will see how I can modify your script. Thank you so much.

Re: concatenate data tables on the go

uday_guntupalli — Thu, 14 Sep 2017 23:49:50 GMT

@vishwasanj :
Please use the following code to fix your problem .

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );

n = 5 ; // Random # of iterations 

for (i = 1 , i <= n , i++, 
	  RowsToSelect = Random Index(40,5);
	  dt << Select Rows(RowsToSelect); 
	  dt << Subset("");  
    );
   
  
// In your case data is coming from a file 
// So I am closing out the source table here that I opened for the purpose of demonstration 
Close(dt,"No Save"); 
    
// Make a list of all open Data tables 
openDTs = List();
For( i = 1, i <= N Table(), i++,
	Insert Into( openDTs, Data Table( i ) );
   );
// Concatenate
for(i = 1 ,i <= N Table(), i++,
		dt_Results = openDTs[i];
		//Close(openDTs[i]);
		, 
		dt_Results << Concatenate(openDTs[i],"Append To First Table");
		Close(openDTs[i]);
   ); 
dt_Results << Set Name("Results");