Our World Statistics Day conversations have been a great reminder of how much statistics can inform our lives. Do you have an example of how statistics has made a difference in your life? Share your story with the Community!
Choose Language Hide Translation Bar
Highlighted
Level VI

## Select rows in one data table using rows from another

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User

## Re: Select rows in one data table using rows from another

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",
New Column( "lot",
Character,
"Nominal",
Set Values( {"a", "b", "c", "d", "e", "a", "b", "c"} )
)
);

dtSmall = New Table( "Small",
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",
New Column( "lot",
Character,
"Nominal",
Set Values( {"a", "b", "c", "d", "e", "a", "b", "c"} )
)
);

dtSmall = New Table( "Small",
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" );``````
Jim
2 REPLIES 2
Highlighted
Super User

## Re: Select rows in one data table using rows from another

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",
New Column( "lot",
Character,
"Nominal",
Set Values( {"a", "b", "c", "d", "e", "a", "b", "c"} )
)
);

dtSmall = New Table( "Small",
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",
New Column( "lot",
Character,
"Nominal",
Set Values( {"a", "b", "c", "d", "e", "a", "b", "c"} )
)
);

dtSmall = New Table( "Small",
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" );``````
Jim
Highlighted
Level VI

## Re: Select rows in one data table using rows from another

Hi @txnelson,

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

DS
Article Labels

There are no labels assigned to this post.