topic Re: Joining multiple tables in jsl in Discussions

Joining multiple tables in jsl

powerpuff — Wed, 28 Feb 2018 14:46:20 GMT

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

Re: Joining multiple tables in jsl

Byron_JMP — Wed, 28 Feb 2018 15:12:02 GMT

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

Re: Joining multiple tables in jsl

powerpuff — Wed, 28 Feb 2018 15:17:58 GMT

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?

Re: Joining multiple tables in jsl

powerpuff — Wed, 28 Feb 2018 15:24:14 GMT

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

Re: Joining multiple tables in jsl

Jeff_Perkinson — Wed, 28 Feb 2018 16:20:18 GMT

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.

Re: Joining multiple tables in jsl

powerpuff — Wed, 28 Feb 2018 16:41:44 GMT

Hi Jeff,

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

Re: Joining multiple tables in jsl

Jeff_Perkinson — Wed, 28 Feb 2018 19:31:11 GMT

I assume you mean the Query Builder tool.

Can you demonstrate what you're getting and what you want?

Re: Joining multiple tables in jsl

powerpuff — Wed, 28 Feb 2018 20:29:05 GMT

Hi Jeff,

The preview table in the image is what I am getting

Whereas what I want is something like this:

Re: Joining multiple tables in jsl

uday_guntupalli — Thu, 01 Mar 2018 13:33:50 GMT

@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

Re: Joining multiple tables in jsl

powerpuff — Thu, 01 Mar 2018 15:18:06 GMT

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?

Re: Joining multiple tables in jsl

txnelson — Thu, 01 Mar 2018 16:34:05 GMT

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");

Re: Joining multiple tables in jsl

powerpuff — Thu, 01 Mar 2018 18:05:24 GMT

That is just awesome!!! Thanks a lot.