Joining multiple tables and output

[#1]

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.

[#2 · Accepted 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;

[#3]

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

[#4 · Accepted solution]

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

[#5]

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

[#6]

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.

[#7 · Accepted 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

[#8]

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