Select rows in one data table using rows from another

[#1]

Hi JMP Community,

  I have two different data tables where they share a column in common, Lot number (Lot for short). (Running JMP Pro 14.1.0 on W10)

  The data tables are from two different sources and have different number of rows in them. I want to use one of the data tables to select the matching rows in the other data table. The values in Lot are continuous/numeric. dt1 has 1430 rows and dt2 has 158. There are more lots in dt1 than dt2, so I need to use dt2 to select only the matching lots in dt1.

  I thought I could do a simple:  dt1<<Select Where(dt2:Lot==dt1:Lot), but this doesn't work. It says that it's scriptable, but nothing happens.

  I think I can do it using two for loops, where for each row in dt1, it would loop through dt2 to see where they are equal, unfortunately this results in 225940 comparisons, which I thought would be easy, but is taking JMP far longer than I imagined.

  This is the selecting code I wrote:

For(l=1, l<=NRows(dt1), l++,
  For(i=1, 1<=NRows(dt2), i++,
    dt1<<Select Where(dt1:Lot[l]==dt2:Lot[i])
  );
);

  But it doesn't work. It seems to be caught in an infinite loop (had to force quit JMP).

  I did a little reading while writing this post, and since I need to keep all previously selected rows, I need to invoke the Current Selection("extend") option. So, I definitely know the above code won't work, but it should have done something.

  There must be a faster what to do this than For Loops, something more elegant.

  Any feedback is much appreciated.

Thanks!,

DS

[#2 · Accepted solution]

Here are 2 ways to do this.  The first method uses your approach, however, it will be slow with large data tables

Names Default To Here( 1 );
// create sample data
dtMain = New Table( "Main",
	Add Rows( 2 ),
	New Column( "lot",
		Character,
		"Nominal",
		Set Values( {"a", "b", "c", "d", "e", "a", "b", "c"} )
	)
);

dtSmall = New Table( "Small",
	Add Rows( 2 ),
	New Column( "lot",
		Character,
		"Nominal",
		Set Values( {"a", "e"} ),
		Set Display Width( 49 )
	)
);

Names Default To Here( 1 );
dtMain << clear select;

For( i = 1, i <= N Rows( dtSmall ), i++,
	dtMain << select where(
		dtMain:Lot == dtSmall:Lot[i],
		current selection( "extend" )
	)
);

The second one will work more efficiently

Names Default To Here( 1 );
// create sample data
dtMain = New Table( "Main",
	Add Rows( 2 ),
	New Column( "lot",
		Character,
		"Nominal",
		Set Values( {"a", "b", "c", "d", "e", "a", "b", "c"} )
	)
);

dtSmall = New Table( "Small",
	Add Rows( 2 ),
	New Column( "lot",
		Character,
		"Nominal",
		Set Values( {"a", "e"} ),
		Set Display Width( 49 )
	)
);

// Create a new column as a Select indicator
dtSmall << New Column( "Select", set each value( 1 ) );

// Update the Main table with values from the Small table
dtMain << Update( With( dtSmall ), Match Columns( :lot = :lot ) );

// Select the matching columns
dtMain << select where( :select == 1 );

// get rid of select columns
dtMain << delete columns( "Select" );
dtSmall << delete columns( "Select" );

[#3]

Hi @txnelson,

I tried the second one, and it worked great! Thanks for the solution.

DS