Joining different tables with a general script

[#1]

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?

[#2]

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.

[#3]

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?

[#4 · Accepted solution]

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!

[#5]

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