Is there a "Virtual Concatenate"?

Question

Hello!  I am trying to combine 8 tables together and update automatically if the original table (be it excel or JMP data table).

* The 8 tables have the same column names and properties, they just have different data sets, so what I am technically trying to do is concatenate all of them together. 
* I also need the joined table (that the 8 feed into) to be able to update if the data in the feeder tables are added/removed, etc.

I know there is a "join" that can create a database which will virtually update with changes to the input data tables, but that function is not working for my needs because it just replaces the data ("join" function on JMP); it does not concatenate.

Is there a function to concatenate 8 data tables (preferably excel) virtually such that the concatenated data table (the one with all 8 original data tables combined) will update if the feeder data tables are changed?

Thank you in advance!

Edit: For more detail on the updates -- we receive data back on an irregular interval (some tests take longer), so not all of the results are available before we start analyzing some of the data.  We expect to have updates to the data (to the individual 8 data tables) while analyzing the data (in the combined/concatenated table). So ideally we want to update the concatenated data table automatically (if any part of the 8 individual data tables are updated) while maintaining the analysis or graphing scripts on the side bar.

ih · Answer

I don't know of a tool analogous to a virtual join either, but it sounds like you could accomplish this with a not-too-complex script utilizing the traditional concatenate.  Here is the general approach I would start with:

Names Default to here(1);

//reference the current data table (the one you are already working in)
dtLatest = current data table();

//list of all source files and variable to hold references to source tables
SourceFile = {"C:\file1.xlsx", "C:\file2.xlsx", ... };
dtSource = list();

//If all files have the same format, use a function to open them all
openfile = Function({fn},
	//Open function, copy this from the source script after opening a file interactively
	dt = Open(
		fn,
		Worksheets( "Sheet 1" ),
		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( 1 ),
			Multiple Series Stack( 0 ),
			Import Cell Colors( 0 ),
			Limit Column Detect( 0 ),
			Column Separator String( "-" )
		)
	);
	dt << New Column(..); //add any more columns, filter or clean data as appropriate
	dt;
);

//Open source tables
for( f=1, f<= n items(SourceFile), f++, 
	dtSource = openfile( SourceFile[f] )
);

//Join all the files together
dtJoined = Concatenate(dtSource);

//Close the source tables
dtSource << Close Window()

//In the source table, delete all rows
dtLatest << Select all Rows;
dtLatest << Delete Rows;

//Concatenate the new rows again
dtLatest << Concatenate( dtJoined );

AU · Answer

Thanks @gzmorgan0 for the suggestions, I added some more details.

Ah, ok, thanks :(  A bit unfortunate.  I'm not familiar with SQL, but I'm open to learning/trying it out if it's simple/straightforward.

gzmorgan0 · Answer

@AU ,

I do not know of a JMP built-in option to do what you want, but an SQL master might have a method to do what I think you want. I am not sure what your expectation/use case happens to be.

Do you expect to have updates while analyzing the data? Do you have analyses that you want updated? Or is this file/analysis expected to be updated at some regular interval of time?

If you provide a more detailed description of what you are expecting (trying to do), then the community might be able to provide you with a solution, even if it isn't a JMP built-in solution.