nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Joining different tables with a general script

Tina442

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?

Accepted answers

Tina442

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!

jthi

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.

All comments

jthi

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.

Tina442

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?

Tina442

I figured out the script:

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

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

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

jthi

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