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

[#1]

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

[#2 · Accepted solution]

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

[#3]

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

[#4]

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.