How to compare two tables

Help
1

Hi All a quick question , I want to compare two data tables having 2 columns in common (ID and Sub ID) and third column amount, I want to find all the rows where the ID and Sub ID are equal but amounts are different , and I also want to cover those ID+Sub ID combinations which are present in one sheet but missing in others , what would be the fastest way to do this ?

1 Like
2

The better way to achieve the result is to use LINQ queries.
Just google it, you will get the LINQ queries.

Regards,
Karthik Byggari

3

@KarthikByggari could you please give an example if possible ? I was able to achieve the same with a do while loop , however would be really interested to know if there is a simpler way

4

I did go through them, just curious on how such queries will be executed within UI path using VB.net

5

using Assign activities.
output_variable = linq_query

6

Hi @Ashish_Mehra

Check this linq query

from table1 in dt1.AsEnumerable()
                    join table2 in dt2.AsEnumerable() on table1.Field<int>("ColumnA") equals table2.Field<int>("ColumnA")
                    where table1.Field<int>("ColumnB") == table2.Field<int>("ColumnB") || table1.Field<string>("ColumnC") == table2.Field<string>("ColumnC") || table1.Field<object>("ColumnD") == table2.Field<object>("ColumnD")
                    select table1;

Thanks
Ashwin.S

1 Like
7

Thanks a lot @AshwinS2

8

@AshwinS2 would the above query be assigned to a data table variable ? just confirming and then can use write range to paste it into an excel .

Thanks for the help !

9

Hi @Ashish_Mehra

Yes you need to assign to a datatable variable and use output datatable and print the data

Thanks
Ashwin S

10

Hi
To be very simple and precise
we can use JOIN DATATABLE ACTIVITY
with either left or right join method used, which will give us this

for more details on this

Cheers @Ashish_Mehra

1 Like
11

@Palaniyappan thanks a lot, I had used join initially and worked, but was just curious about the Linq queries functionality, I think It can save a lot of container space and can be used for joining multiple tables too, whereas Join command only allows two data tables to be joined

12

Building your own LINQ is more versatile, as the Join activity is just using a preset linq query in the background. The built-in activity is nice and quick to use when it is suitable for the job though

13

Hi @Ashish_Mehra Here is a detailed article on that :slight_smile:

Regards,

1 Like