BookmarkSubscribe
Choose Language Hide Translation Bar
Highlighted
powerpuff
Community Trekker

Joining multiple tables in jsl

For example, I have 3 tables,

Table 1:

Example.PNG 

 

Table 2:

 

example1.PNG

 

Table 3:

 

Example3.PNG

 

The final table I want should look something like this:

FinalTable.PNG

 

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

0 Kudos
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Joining multiple tables in jsl

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

powerpuff.GIF

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
Byron_JMP
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
0 Kudos
powerpuff
Community Trekker

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?

0 Kudos
powerpuff
Community Trekker

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

0 Kudos
Jeff_Perkinson
Community Manager 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.

JMPScreenSnapz178.png

 

-Jeff
0 Kudos
powerpuff
Community Trekker

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.. 

0 Kudos
Jeff_Perkinson
Community Manager 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
0 Kudos
powerpuff
Community Trekker

Re: Joining multiple tables in jsl

Hi Jeff,

The preview table in the image is what I am getting

 

Preview.PNG

 

Whereas what I want is something like this:

 

try.PNG

0 Kudos
uday_guntupalli
Community Trekker

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


image.png






 

Best
Uday
0 Kudos
powerpuff
Community Trekker

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?

0 Kudos