Compare the values of rows with same ID in different tables

Help
1

Hi, I have two tables with the same structure. The columns are ID1, ID2, ValueA, ValueB.
Now I would like to compare the rows with the same ID1 AND ID2 between the two tables and add the differences to a third table. (This shows an employee potential errors that he needs to check)
E.g.
Table 1:
ID1 ID2 ValueA ValueB
ABC EFG 2 4
XYZ ABC 3 9
SSC FDF 1 1

Table 1:
ID1 ID2 ValueA ValueB
ABC EFG 2 4
XYZ ABC 5 9
JKL ABC 3 9

Output
Differences Table:
ID1 ID2 ValueA1 ValueA2 ValueB1 ValueB2
XYZ ABC 3 5 - -

2

Hi @Jizh - Try the below mentioned query

(From x In Table1.AsEnumerable() where Not (From a In ReadDT.AsEnumerable() Join b In Table2.AsEnumerable() On a(“ID1”).ToString() Equals b(“ID1”).ToString() and a(“ID2”).ToString() Equals b(“ID2”).ToString() select a).Contains(x) select x).CopyToDataTable()

Thanks,
AK

1 Like
3

Thanks a lot, I will test that solution with my tables :slight_smile:

4

@AnandKumar26
What is ReadDT in your example?

5

Its Table1. Sorry

Thanks,
AK

1 Like
6

@AnandKumar26 tested it with the following example

Table 1

ID1 ID2 ValueA ValueB
aa bb 1 1
aa cc 2 2
aa dd 3 3

Table 2

ID1 ID2 ValueA ValueB
aa bb 1 1
aa cc 2 4
aa qq 9 9

And it gave me
aa dd 3 3
Whereas I wanted
aa cc 2 4 → because there the IDs are the same but the values are different
Sorry, if I didn’t make the problem clear in the beginning

7

Would you like to check each column? or any specific columns?

Thanks,
AK

8

EDIT: Like in the examples I would like to see differences in the column “ValueA” and “ValueB”. Rows should only be compared if their ID1 AND ID2 are equal.

9

@AnandKumar26 I made some clarifications in my previous reply. Is it clear what I would like to achieve?

10

Which is your first priority? ID or Value?

Thanks,
AK

1 Like
11

The IDs have to match so that the values can be compared.
If both of the IDs of a row are the same in both tables then their values have to be the same (otherwise add to difference table).
But if the values are the same, the IDs do not necessarily have to be the same.
IDs could be shoe name and shoe size, then the values would be quantity in stock and price.
So if I look at a particular shoe in a particular size, then the values for stock and price have to be the same in both tables.

12

Little conflict to understand with what you have explained. As per my understanding, for each row IDs and Values should match is what the output?

Thanks,
AK

13

Let’s make the example a bit simpler then. Let’s assume there was only one column with a key and one with a value. Then one would take the first row in table1 and search for the same ID in table2. If the values of both rows are the same it is skipped, if the values are different, then the value of the table2 is added to the third table.
My previous example is the same only that the unique key consists of two IDs and there are two values to look at. The first value is compared with the first and the second with the second.

14

hi @Jizh

I’ll suggest you to use .Any method of Linq (to compare data) as shown in below:

but you have to mention all those columns ID1, ID2, ValueA and ValueB in query using OR.

I’m attaching my workflow here, so that you’ll get the idea.
Workflow : Unmatched1.xaml (15.9 KB)

1 2
Result—> result

2 Likes
15

Hi @Jizh - Use something like this.

Match ID DT :- DT1 = (From x In Table1.AsEnumerable() Where (From a In Table1.AsEnumerable() Join b In Table2.AsEnumerable() On a(“ID1”).ToString() Equals b(“ID1”).ToString() And a(“ID2”).ToString() Equals b(“ID2”).ToString() Select a).Contains(x) Select x).CopyToDataTable()

Matches only the IDs first then,

Match Values DT :- DT2 = (From x In DT1.AsEnumerable() Where Not (From a In DT1.AsEnumerable() Join b In Table2.AsEnumerable() On a(“ValueA”).ToString() Equals b(“ValueA”).ToString() and a(“ValueB”).ToString() Equals b(“ValueB”).ToString() Select a).Contains(x) Select x).CopyToDataTable()

Try this, or the the code with one single query which has been shared in this page also can be used.

Thanks,
AK

1 Like
16

@AnandKumar26 @samir Thanks a lot guys you were a great help :slight_smile:

1 Like
17

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.