Joining multiple tables in jsl

powerpuff

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

txnelson

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");