Joining multiple tables and output

adam

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.

txnelson

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;