Join, also known as horizontal join or concatenate, combines data tables side to side.
dt << Join( // Message to the first table
With(dataTable), // The secondary data table
Select(columns), // Selects columns from the main table
// to add to the output table.
Select With(columns), // Selects 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, // Merges 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), // Adds the Match Flag column from the joined data table when you are matching by column.
Update, // Replaces the data in the main table with the corresponding data from the secondary table.
// options for each table:
Drop Multiples(Boolean, Boolean), // Includes all rows from the data table
Include Non Matches(Boolean, Boolean), // Includes non-matching rows
Preserve Main Table Order(), // Maintains 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" )
);
sortedPart2 = part2 << Sort( By( :name ), Output Table Name( "Sorted_Big Class" ) );
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.