BookmarkSubscribe
Choose Language Hide Translation Bar
adam
Community Trekker

Joining multiple tables and output

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.

0 Kudos
3 ACCEPTED SOLUTIONS

Accepted Solutions
txnelson
Super User

Re: Joining multiple tables and output

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

Re: Joining multiple tables and output

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

Re: Joining multiple tables and output

To pile onto @txnelson and my colleague @bill_worley 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 7
txnelson
Super User

Re: Joining multiple tables and output

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
Community Trekker

Re: Joining multiple tables and output

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

Re: Joining multiple tables and output

To pile onto @txnelson and my colleague @bill_worley 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
Community Trekker

Re: Joining multiple tables and output

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

0 Kudos

Re: Joining multiple tables and output

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.

Re: Joining multiple tables and output

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
Community Trekker

Re: Joining multiple tables and output

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

0 Kudos