topic Re: Joining differnt size data sets in Discussions

Joining differnt size data sets

Sciguy_1 — Thu, 13 Jun 2019 16:06:37 GMT

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.

Re: Joining differnt size data sets

txnelson — Thu, 13 Jun 2019 16:39:32 GMT

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

Re: Joining differnt size data sets

Sciguy_1 — Thu, 13 Jun 2019 19:20:36 GMT

Thank you for the help on this! I was not checking to include non-matches. Got it working thank you.

Re: Joining differnt size data sets

pmroz — Fri, 14 Jun 2019 19:07:34 GMT

You can also use the Tables > Update command.

dt1 << Update(
	With( dt2 ),
	Match Columns( :JAR ID = :JAR ID, :SEQ ID = :SEQ ID ),
	Add Columns from Update table( :TOTE ID )
);