For the latest version of JMP Help, visit JMP.com/help.


Scripting Guide > Data Tables > Advanced Data Table Scripting > Horizontally Concatenate Data Tables
Publication date: 06/21/2023

Horizontally Concatenate Data Tables

The Join message combines data tables side to side, horizontally joining the data.

dt << Join( // message to the first table
	With( dataTable ), // the secondary data table
	Select( columns ), /* select columns from the main table
		to add to the output table */
	Select With( columns ), /* select columns from the secondary table to
		add to the output table */
	// join type; alternatives are
	Cartesian join, 	By Row Number, By matching columns(col1=col2, ...)
	Merge Same Name Columns, // merge columns with the same name
	Copy Formula( 0 ), // on by default; 0 turns it off
	Suppress Formula Evaluation( 0 ), // on by default; 0 turns it off
	Match Flag( 1 ), /* add the Match Flag column from the joined data table when you are matching by column */
	Update, /* replace the data in the main table with the corresponding data from the secondary table */
	// options for each table:
	Drop Multiples( Boolean, Boolean ), // include all rows from the data table
	Include Non Matches( Boolean, Boolean ), // include non-matching rows
	Preserve Main Table Order(), // maintain the order of the original data
	Output Table Name( "name" )); // the resulting table

To try this, first break Big Class.jmp into two parts:

dt = Open( "$SAMPLE_DATA/Big Class.jmp" );
part1 = dt << Subset(
	Columns( :name, :age, :height ),
	Output Table Name( "Big Class 1" )
);
part2 = dt << Subset(
	Columns( :name, :sex, :weight ),
	Output Table Name( "Big Class 2" )
);

To make it a realistic experiment, rearrange the rows in part 2:

sortedPart2 = part2 << Sort( By( :name ), Output Table Name( "Sorted_Big Class" ) );

Now you have a data set in two separate chunks, and the rows are not in the same order, but you can join them together by matching on the column that the two chunks have in common.

part1 << Join(
	With( sortedPart2 ),
	By Matching Columns( :name == :name ),
	Preserve Main Table Order();
	Output Table Name( "Joined Parts" );
);

The resulting table has two copies of the name variable, one from each part, and you can inspect these to see how Join worked. Notice that you now have four Robert rows, because each part had two Robert rows (there were two Roberts in the original table) and Join formed all possible combinations.

Tip: To maintain the order of the original data table in the joined table (instead of sorting by the matching columns), include Preserve Main Table Order(). This function speeds up the joining process.

See JSL Messages in the JSL Syntax Reference for more information about the Join arguments.

Want more information? Have questions? Get answers in the JMP User Community (community.jmp.com).