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.