topic Re: Joining different tables with a general script in Discussions

Joining different tables with a general script

Tina442 — Fri, 17 Oct 2025 10:43:27 GMT

Hi,

I know we can join tables easily under the Table header, but I'm trying to develop a script that would be able to join tables without having to specify the column names.

I have three data tables. The first two columns are always the same, but the others can always change its name. It gets more complicated because for a different batch, I can the same three data tables but the name of the first two columns could change. So I am trying to join all these three tables using the first two column position as the linking column and add the other columns altogether into one big data table.

I tried using variables or column positions but it didn't work. Tried also with the New SQL Query() function but I think that also has to specify columns.

Does anyone have an idea on how we can develop a general script that can simply join three different tables into one?

Re: Joining different tables with a general script

jthi — Fri, 17 Oct 2025 10:52:53 GMT

Easiest would be to write a script which would rename the columns to be the same but it is also possible to write a script to use different column names.

Re: Joining different tables with a general script

Tina442 — Fri, 17 Oct 2025 11:53:49 GMT

Is there a way to make the script 'general'? So you don't have to specify the column names within the script?

Maybe have the script take the column names from the data itself?

Re: Joining different tables with a general script

Tina442 — Fri, 17 Oct 2025 13:19:46 GMT

I figured out the script:

dt = {Data Table ("X"), Data Table ("Y"), Data Table ("Z")};

// Start with the first table as base
joinedDT = dt[1];
// Sequentially join each remaining table to the accumulated result
For(i = 2, i <= N Items(dt), i++,
tmpDT = joinedDT << Join(
With(responseTables[i]),
Drop Multiples(1,1),
Include Nonmatches(0,0),
Merge Same Name Columns,
Preserve Main Table Order(1)
);

// Rename the intermediate table
tmpDT << Set Name("Intermediary Data Table");

// Replace joinedDT with the newly joined table for next iteration
joinedDT = tmpDT;
);

I wished there were more information in the scripting index about this part of the Join function:

Drop Multiples(1,1),
Include Nonmatches(0,0),
Merge Same Name Columns,
Preserve Main Table Order(1)

I had to go trial and error to figure it out!

Re: Joining different tables with a general script

jthi — Fri, 17 Oct 2025 16:28:42 GMT

Doing it interactively in JMP is generally the way to learn things like this in JMP. And you can go to Help page from Scripting Index and it does have more explanation Using JMP > Reshape Your Data > Join Data Tables

Re: Joining different tables with a general script

Tina442 — Mon, 20 Oct 2025 09:18:06 GMT

Thank you, I didn't know it goes directly to the support page. That's useful, but it doesn't have an explanation of the script. That's what I was looking for since I wanted to script it to build an add-in that does it automatically.

Re: Joining different tables with a general script

jthi — Tue, 21 Oct 2025 03:29:54 GMT

JSL Syntax Reference is sometimes better than the Help page Scripting Guide opens (sometimes it does refer to JSL Syntax Reference)

JSL Syntax Reference > JSL Messages > Data Table Messages and look for <<Join(

You can find .pdf version if JSL Syntax Reference (I prefer that JMP Help's search is something else...) from JMP documentation.

Also, help page does explain the options but it can be a bit more difficult to fully understand it from the platform help page:

Drop Multiples(1,1),

Include Nonmatches(0,0),

Merge Same Name Columns,

Preserve Main Table Order(1)

In general, trial and error (or letting JMP script for you) is good way to start learning JSL.

Re: Joining different tables with a general script

Tina442 — Thu, 23 Oct 2025 10:49:31 GMT

That's very helpful, thank you very much for pointing me to the right direction!