Subscribe Bookmark RSS Feed

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

uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

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

12956_pastedImage_0.png

Jim
3 REPLIES
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

12956_pastedImage_0.png

Jim
uday_guntupalli

Community Trekker

Joined:

Sep 15, 2014

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

Joined:

Jun 22, 2012

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