topic Re: Joining multiple tables and output in Discussions

Joining multiple tables and output

adam — Tue, 07 Nov 2017 08:49:21 GMT

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.

Re: Joining multiple tables and output

txnelson — Tue, 07 Nov 2017 09:31:23 GMT

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;

Re: Joining multiple tables and output

adam — Tue, 07 Nov 2017 10:10:59 GMT

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

Re: Joining multiple tables and output

Bill_Worley — Tue, 07 Nov 2017 13:44:07 GMT

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

adam — Wed, 08 Nov 2017 01:35:14 GMT

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

Re: Joining multiple tables and output

Peter_Bartell — Wed, 08 Nov 2017 12:27:30 GMT

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

Re: Joining multiple tables and output

adam — Thu, 09 Nov 2017 01:21:44 GMT

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

Re: Joining multiple tables and output

Peter_Bartell — Thu, 09 Nov 2017 01:28:37 GMT

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.