For example, I have 3 tables,
Table 1:
Table 2:
Table 3:
The final table I want should look something like this:
I tried using the 'Join Table' option but it doesn't give the output I want. Is there any other way to do this? Thanks
Accepted Solutions
Here is a script that joins your data as perscribed. The final table is showned below
names default to here(1);
dt1=data table("Table 1");
dt2=data table("Table 2");
dt3=data table("Table 3");
// Add an instance counter for each table
// Which will allow the joining by specific rows
dt1<<new column("instance",formula(If(lag(:Name)!=:Name,st=0);st=st+1;));
dt2<<new column("instance",formula(If(lag(:Name)!=:Name,st=0);st=st+1;));
dt3<<new column("instance",formula(If(lag(:Name)!=:Name,st=0);st=st+1;));
// Join the first tables
dt4=dt1 << Join(
With( dt3 ),
Merge Same Name Columns,
By Matching Columns( :Name = :Name, :instance = :instance ),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
);
// Join the resulting table with the other table
dt5=dt4 << Join(
Output Table Name("Final Form of Data"),
With( dt2 ),
Merge Same Name Columns,
By Matching Columns( :Name = :Name, :instance = :instance ),
Drop multiples( 0, 0 ),
Include Nonmatches( 1, 1 ),
Preserve main table order( 1 )
);
// Cleanup the results
close(dt4,nosave);
dt1 << delete columns("instance");
dt2 << delete columns("instance");
dt3 << delete columns("instance");
dt5 << delete columns("instance", "Match Flag");
Community Manager
Nearly everything you can do interactively you can do through JSL and JMP will usually give you the script to do it.
Look in the red triangle hotspot at the top of the JMP Query Builder and you'll find the options to get the script.
Hi Jeff,
The preview table in the image is what I am getting
Whereas what I want is something like this:
@powerpuff,
Maybe something like this ?
Clear Log(); Clear Globals();
// I am assuming you have the data tables open. If not you will need to open the data tables and capture their references
// Getting references to the data tables
dt1 = Data Table("Courses");
dt2 = Data Table("HeightAndWeight");
dt3 = Data Table("Interests");
// Updating data table 1 to get all data in one place
dt1 << Update(
With(dt2 ),
Match Columns( :Name = :Name ),
Add Columns from Update table( :Height, :Weight )
);
dt1 << Update(
With(dt3 ),
Match Columns( :Name = :Name ),
Add Columns from Update table( :Interests )
);Please note that the resulting table will look like this which is a slight modification of your ask but fair representation of the data. Is this what you already had when you tried "Join Tables" and is this not what you wanted ? I see that in the desired output you have shared , you are essentially concatenating the data horizontally to some extent
