cancel
Showing results for 
Show  only  | Search instead for 
Did you mean: 
JMP is taking Discovery online, April 16 and 18. Register today and join us for interactive sessions featuring popular presentation topics, networking, and discussions with the experts.
Choose Language Hide Translation Bar
Sciguy_1
Level III

Joining differnt size data sets

1.JPG2.JPG

 

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.

1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: Joining differnt size data sets

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

View solution in original post

3 REPLIES 3
txnelson
Super User

Re: Joining differnt size data sets

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" )
);
Jim
Sciguy_1
Level III

Re: Joining differnt size data sets

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

Re: Joining differnt size data sets

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