Subscribe Bookmark RSS Feed

Joining multiple tables and output

adam

Contributor

Joined:

Sep 20, 2017

Hi,

 

I tried the Join table function and I think it's able to join one at a time(eg. dt1 join with dt2) and we will get an output table(dtnew). How can I use the now output table(eg. dtnew) to Join with another table(eg. dt3). 

 

Thank you.

3 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Here is one method that create separate tables for each join.

Names Default To Here( 1 );

// Create the original table
dtBase = New Table( "Base Table",
	Add Rows( 10 ),
	New Column( "name",
		character,
		Set Values( {"KATIE", "LOUISE", "JANE", "JACLYN", "LILLIE", "TIM", "JAMES", "ROBERT", "BARBARA", "ALICE"} )
	),
	New Column( "age", Set Values( [12, 12, 12, 12, 12, 12, 12, 12, 13, 13] ) ),
	New Column( "sex", character, Set Values( {"F", "F", "F", "F", "F", "M", "M", "M", "F", "F"} ) )
);

// Create a second table
dt2 = New Table( "Height",
	Add Rows( 10 ),
	New Column( "name",
		character,
		Set Values( {"KATIE", "LOUISE", "JANE", "JACLYN", "LILLIE", "TIM", "JAMES", "ROBERT", "BARBARA", "ALICE"} )
	),
	New Column( "height", Set Values( [59, 61, 55, 66, 52, 60, 61, 51, 60, 61] ) ),
	Set Label Columns( :name )
);

// Join the two tables and create a reference to the new table
dtjoined = dtBase << Join(
	With( dt2 ),
	By Matching Columns( :name = :name ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 ),
	Merge same name columns( 1 ),
	Match Flag( 0 )
);

// Close the nolonger needed tables and set the base table reference to the new table
Close( dtBase, nosave );
Close( dt2, nosave );
dtBase = dtjoined;

// Create another table to be joined
dt3 = New Table( "Weight",
	Add Rows( 10 ),
	New Column( "name",
		character,
		Set Values( {"KATIE", "LOUISE", "JANE", "JACLYN", "LILLIE", "TIM", "JAMES", "ROBERT", "BARBARA", "ALICE"} )
	),
	New Column( "weight", Set Values( [95, 123, 74, 145, 64, 84, 128, 79, 112, 107] ) ),
	Set Label Columns( :name )
);

// Join the new table
dtjoined = dtBase << Join(
	With( dt3 ),
	By Matching Columns( :name = :name ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 ),
	Merge same name columns( 1 ),
	Match Flag( 0 )
);

// Close the nolonger needed tables and set the base table reference to the new table
Close( dtBase, nosave );
Close( dt3, nosave );
dtBase = dtjoined;
Jim
billw_jmp

Staff

Joined:

Jul 2, 2014

Solution

I am not sure if this is your intention, but if what you want is to join multiple tables and you are using JMP 13 you can go to the Tables drop down and select JMP Query Builder.  From there you can join up to 60 tables at once instead of having to script the a join. 

 

HTH

Peter_Bartell

Joined:

Jun 5, 2014

Solution

To pile onto @txnelson and my colleague @billw_jmp still yet another potential option is to use JMP 13's Virtual Table join capability as well. Here's the link to the JMP online documentation for the virtual join capability.

 

http://www.jmp.com/support/help/13-2/Virtual_Join_Properties.shtml

 

 

7 REPLIES
txnelson

Super User

Joined:

Jun 22, 2012

Solution

Here is one method that create separate tables for each join.

Names Default To Here( 1 );

// Create the original table
dtBase = New Table( "Base Table",
	Add Rows( 10 ),
	New Column( "name",
		character,
		Set Values( {"KATIE", "LOUISE", "JANE", "JACLYN", "LILLIE", "TIM", "JAMES", "ROBERT", "BARBARA", "ALICE"} )
	),
	New Column( "age", Set Values( [12, 12, 12, 12, 12, 12, 12, 12, 13, 13] ) ),
	New Column( "sex", character, Set Values( {"F", "F", "F", "F", "F", "M", "M", "M", "F", "F"} ) )
);

// Create a second table
dt2 = New Table( "Height",
	Add Rows( 10 ),
	New Column( "name",
		character,
		Set Values( {"KATIE", "LOUISE", "JANE", "JACLYN", "LILLIE", "TIM", "JAMES", "ROBERT", "BARBARA", "ALICE"} )
	),
	New Column( "height", Set Values( [59, 61, 55, 66, 52, 60, 61, 51, 60, 61] ) ),
	Set Label Columns( :name )
);

// Join the two tables and create a reference to the new table
dtjoined = dtBase << Join(
	With( dt2 ),
	By Matching Columns( :name = :name ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 ),
	Merge same name columns( 1 ),
	Match Flag( 0 )
);

// Close the nolonger needed tables and set the base table reference to the new table
Close( dtBase, nosave );
Close( dt2, nosave );
dtBase = dtjoined;

// Create another table to be joined
dt3 = New Table( "Weight",
	Add Rows( 10 ),
	New Column( "name",
		character,
		Set Values( {"KATIE", "LOUISE", "JANE", "JACLYN", "LILLIE", "TIM", "JAMES", "ROBERT", "BARBARA", "ALICE"} )
	),
	New Column( "weight", Set Values( [95, 123, 74, 145, 64, 84, 128, 79, 112, 107] ) ),
	Set Label Columns( :name )
);

// Join the new table
dtjoined = dtBase << Join(
	With( dt3 ),
	By Matching Columns( :name = :name ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 0, 0 ),
	Preserve main table order( 1 ),
	Merge same name columns( 1 ),
	Match Flag( 0 )
);

// Close the nolonger needed tables and set the base table reference to the new table
Close( dtBase, nosave );
Close( dt3, nosave );
dtBase = dtjoined;
Jim
adam

Contributor

Joined:

Sep 20, 2017

Thanks, that's wonderful Jim ! I managed write one script that is similar but a little complex :) You just made it easier to for me to understand. Appreciate that..
Peter_Bartell

Joined:

Jun 5, 2014

Solution

To pile onto @txnelson and my colleague @billw_jmp still yet another potential option is to use JMP 13's Virtual Table join capability as well. Here's the link to the JMP online documentation for the virtual join capability.

 

http://www.jmp.com/support/help/13-2/Virtual_Join_Properties.shtml

 

 

adam

Contributor

Joined:

Sep 20, 2017

That would helps too and it saves memory. Thanks Pete :)

Peter_Bartell

Joined:

Jun 5, 2014

One of the primary customer driven needs that was addressed by the JMP version 13 virtual join capability was the memory conservation footprint. So glad this works for your situation.

billw_jmp

Staff

Joined:

Jul 2, 2014

Solution

I am not sure if this is your intention, but if what you want is to join multiple tables and you are using JMP 13 you can go to the Tables drop down and select JMP Query Builder.  From there you can join up to 60 tables at once instead of having to script the a join. 

 

HTH

adam

Contributor

Joined:

Sep 20, 2017

Thanks HTH ! That helps too. Never thought about it. Learning another new stuff :-)