Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level IV

## Joining multiple tables in jsl

For example, I have 3 tables,

Table 1:

Table 2:

Table 3:

The final table I want should look something like this:

I tried using the 'Join Table' option but it doesn't give the output I want. Is there any other way to do this? Thanks

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Joining multiple tables in jsl

Here is a script that joins your data as perscribed.  The final table is showned below

``````names default to here(1);
dt1=data table("Table 1");
dt2=data table("Table 2");
dt3=data table("Table 3");

// Add an instance counter for each table
// Which will allow the joining by specific rows
dt1<<new column("instance",formula(If(lag(:Name)!=:Name,st=0);st=st+1;));
dt2<<new column("instance",formula(If(lag(:Name)!=:Name,st=0);st=st+1;));
dt3<<new column("instance",formula(If(lag(:Name)!=:Name,st=0);st=st+1;));

// Join the first tables
dt4=dt1 << Join(
With( dt3 ),
Merge Same Name Columns,
By Matching Columns( :Name = :Name, :instance = :instance ),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
);

// Join the resulting table with the other table
dt5=dt4 << Join(
Output Table Name("Final Form of Data"),
With( dt2 ),
Merge Same Name Columns,
By Matching Columns( :Name = :Name, :instance = :instance ),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
);

// Cleanup the results
close(dt4,nosave);

dt1 << delete columns("instance");
dt2 << delete columns("instance");
dt3 << delete columns("instance");
dt5 << delete columns("instance", "Match Flag");``````
Jim
11 REPLIES 11
Highlighted
Staff

## Re: Joining multiple tables in jsl

This might be a lot easier to do in the table query builder. (Tables/JMP Query Builder)   The it could happen in one step

JMP Systems Engineer, Pharm and BioPharm Sciences
Highlighted
Level IV

## Re: Joining multiple tables in jsl

Hi Byron, I am actually looking at automating this part for all tables I will have to deal with. Is there a way to achieve this in JSL?

Highlighted
Level IV

## Re: Joining multiple tables in jsl

Even though the JMP Query Builder does exactly what I want, I need a way to do it in JSL. Thanks

Highlighted
Community Manager

## Re: Joining multiple tables in jsl

Nearly everything you can do interactively you can do through JSL and JMP will usually give you the script to do it.

Look in the red triangle hotspot at the top of the  JMP Query Builder and you'll find the options to get the script.

-Jeff
Highlighted
Level IV

## Re: Joining multiple tables in jsl

Hi Jeff,

I am sorry, but the JMP Model Builder too doesn't seem to give me the result I want..

Highlighted
Community Manager

## Re: Joining multiple tables in jsl

I assume you mean the Query Builder tool.

Can you demonstrate what you're getting and what you want?
-Jeff
Highlighted
Level IV

## Re: Joining multiple tables in jsl

Hi Jeff,

The preview table in the image is what I am getting

Whereas what I want is something like this:

Highlighted
Level VIII

## Re: Joining multiple tables in jsl

@powerpuff
Maybe something like this ?

``````Clear Log(); Clear Globals();

// I am assuming you have the data tables open. If not you will need to open the data tables and capture their references

// Getting references to the data tables
dt1 = Data Table("Courses");
dt2 = Data Table("HeightAndWeight");
dt3 = Data Table("Interests");

// Updating data table 1 to get all data in one place
dt1 << Update(
With(dt2 ),
Match Columns( :Name = :Name ),
Add Columns from Update table( :Height, :Weight )
);

dt1 << Update(
With(dt3 ),
Match Columns( :Name = :Name ),
Add Columns from Update table( :Interests )
);``````

Please note that the resulting table will look like this which is a slight modification of your ask but fair representation of the data. Is this what you already had when you tried "Join Tables" and is this not what you wanted ? I see that in the desired output you have shared , you are essentially concatenating the data horizontally to some extent

Best
Uday
Highlighted
Level IV

## Re: Joining multiple tables in jsl

Hi Uday,

Thanks for the response but the table in the end result has repetitive values. That was not what I was looking for.. Is there any way I could get the output table as I mentioned in the question?

Article Labels

There are no labels assigned to this post.