BookmarkSubscribe
Choose Language Hide Translation Bar
txnelson
Super User

Re: Joining multiple tables in jsl

Here is a script that joins your data as perscribed.  The final table is showned below

powerpuff.GIF

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");
Jim
powerpuff
Community Trekker

Re: Joining multiple tables in jsl

That is just awesome!!! Thanks a lot.

0 Kudos