Joining differnt size data sets

Sciguy_1

 

I am trying to pull a data point from one data table into another data table. Above is an example. A simple join does not seem to get me where I need to go. The data table on the left in reality is quite large(800K rows). I would like to match between the two data tables based on jar id and seq id. Then create a new column in the table on the left with the TOTE ID and populate it with data from the table on the right (which is much smaller 70k rows) with a successful match. So any given TOTE ID from the data table on the right will get used repeatedly populating the table on the left. Thank you for your assistance.

txnelson

What you want to do is a simple join of the data tables.

     Tables==>Join

Below is the JSL that will do the join, however this can easily be done in an interactive mode

names default to here(1);

// Create Sample data tables
dt1 = New Table( "Base",
	Add Rows( 10 ),
	New Column( "Tube ID",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1, 2, 3, 4, 5, 6, 7, 8, 9, 10] )
	),
	New Column( "JAR ID",
		Character,
		"Nominal",
		Set Values(
			{"XXXY1", "XXXY1", "XXXY2", "XXXY2", "XXXY3", "XXXY3", "XXXY1", "XXXY1",
			"XXXY3", "XXXY3"}
		),
		Set Display Width( 92 )
	),
	New Column( "SEQ ID",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1001, 1001, 1002, 1002, 1003, 1003, 1001, 1001, 1003, 1003] ),
		Set Display Width( 131 )
	)
);
dt2 = New Table( "Lookup",
	Add Rows( 5 ),
	New Column( "JAR ID",
		Character,
		"Nominal",
		Set Values( {"XXXY1", "XXXY2", "XXXY3", "XXXY4", "XXXY5"} ),
		Set Display Width( 72 )
	),
	New Column( "SEQ ID",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [1001, 1002, 1003, 1004, 1005] ),
		Set Display Width( 80 )
	),
	New Column( "TOTE ID",
		Character,
		"Nominal",
		Set Values( {"AAA1", "AAA2", "AAA3", "AAA4", "AAA5"} )
	)
);

// Join the tables
dtJoin = dt1 << Join(
	With( dt2 ),
	Merge Same Name Columns,
	Match Flag( 0 ),
	By Matching Columns( :JAR ID = :JAR ID, :SEQ ID = :SEQ ID ),
	Drop multiples( 0, 0 ),
	Include Nonmatches( 1, 0 ),
	Preserve main table order( 1 ),
	Output Table( "Joined" )
);