topic Re: Merging Multiple Tables in Discussions

Merging Multiple Tables

LargeChipmunk17 — Sat, 10 Jun 2023 23:57:46 GMT

Is there a way in JMP to join multiple tables at once. I have a very large data set and I need pick various data out of different rows and columns for various functions. I can do this with the built-in JMP Query Builder, then save the scripts for individual functions. However, this yields multiple data tables, all with one common column of data. Is there a way to have a script that joins all these tables at once based on the one common column? I'd like a script that I can have saved so I don't have to merge tables one at a time every time I do this.

Re: Merging Multiple Tables

Jed_Campbell — Thu, 01 Dec 2022 21:58:02 GMT

Assuming you have a list with the names of the tables you want to merge and that the column names are all the same, the concatenate() function should work for you. Example below that attempts to merge all open tables:

dtlist = {};

For( i = 1, i <= N Table(), i++,
	dt = data table(i);
	insert into(dtList, dt );
);

dtbigtable = concatenate (dtlist);

Re: Merging Multiple Tables

LargeChipmunk17 — Thu, 01 Dec 2022 22:14:41 GMT

Hi Jed,

Thank you for the quick response. That's close to what I'm looking for, but not quite. Unfortunately, that yields a table similar to the one below:

What I'm trying to achieve is more like what's shown below, so that I can use fit model with the data:

Re: Merging Multiple Tables

Jed_Campbell — Thu, 01 Dec 2022 22:25:48 GMT

You can use the Tabulate platform to get the table you need.

(Data Table( "Untitled 148" ) << Tabulate(
	Add Table(
		Column Table( Analysis Columns( :Column 2, :Column 3, :Column 4, :Column 5 ) ),
		Row Table( Grouping Columns( :Column 1 ) )
	)
)) << Make Into Data Table

Re: Merging Multiple Tables

LargeChipmunk17 — Thu, 01 Dec 2022 22:39:10 GMT

Thank you that fixes my problem!