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
uday_guntupalli
Level VIII

How to check if values in one datatable exist in another datatable

All,

    I have 2 data tables (Table A and Table B) . Table A has a couple of 1000 rows while Table B has a couple of million rows . They have a common column lets say Col C - I want to get the rows of Table B which have matching values in Col C .

for( i = 1, i <= N Rows( TableA ), i++,

        SelectedRows = TableB << Get Rows Where(TalbeB:ColC == TableA:ColC1 );

   );

   While this works to some extent - it takes too long owing to the fact that table B is huge. Is there a better and efficient way to address this problem .

Best

Uday

Best
Uday
1 ACCEPTED SOLUTION

Accepted Solutions
txnelson
Super User

Re: How to check if values in one datatable exist in another datatable

What you want to do, is to run the Join Platform, and match on the columns you specify.  By default, it will create a new data table where only the matching rows remain.

Tables==>Join

12956_pastedImage_0.png

Jim

View solution in original post

3 REPLIES 3
txnelson
Super User

Re: How to check if values in one datatable exist in another datatable

What you want to do, is to run the Join Platform, and match on the columns you specify.  By default, it will create a new data table where only the matching rows remain.

Tables==>Join

12956_pastedImage_0.png

Jim
uday_guntupalli
Level VIII

Re: How to check if values in one datatable exist in another datatable

Jim ,

      Thank you for the helpful idea. If I may ask - I have a follow up question - if I need the number of matches in each case can I capture that outside the Drop Multiples option ?

Best
Uday

Best
Uday
txnelson
Super User

Re: How to check if values in one datatable exist in another datatable

I am not exactly sure what you are asking when you say, "capture that outside of Drop Multiples option".  If the data table created contains all of the data you want to count, you can easily get the count with:

dtjoin = dt << Join(..............);

number of matches = N Rows(dtjoin);

Or you can also use the "Match Flag" option from the input dialog screen to create a column called Match Flag, which will have the values of 1,2 or 3.  If the data is from the first data table, and no match in the second table, the value of Match Flag is 1.  Coming from the second table and no match with the first table, the value is 2, and if it finds a match, it's value is a 3.

Jim