Comaparing Datatables (multiple columns)

Hi everyone,

I’m hoping someone can help guide me towards the solution to the following problem:

I have two Datatables which I need to compare. The following picture has a dummy example of my data where the logic needs to be that: For each row in Table 1, where dates are equal to the dates from Table 2 - if row has a match (Has to match all three columns) in Table 2 then add row to Table 3 - Else write row to Table 4. Then finally removing duplicates from both Table 3 and 4

Best regards,
Reynir

Hi @rjonasson,

Hope you are using IF activity to compare both the excels. In that if condition you can use Excel Application Scope in both “Then” and “Else” conditions so that if the loop goes to “then”, write activity in Table3 and if loop goes to “Else”, write activity in Table4.

Hi Hareesh,

Thank you for the reply. What I’m trying at the moment is - For each row in Datatable 1 and in that for each another one so For each row in Datatable 2, then using a If function inside the second For each loop. So now I basically need the Expression for the IF and ELSE parts for matching the data.

For that you can simply use Add Data Row in both Then and Else conditions with two different data tables. You will have item1 and item2 (assuming) as the row item for table1 and table2, you can provide the input for add data row as item1 or item2 whatever you want.

Instead of looping thru data tables, you can achieve with two LINQ queries.

One is for matched rows
Another is for unmatched rows

The following LINQ queries are in c#. (The syntax in VB.Net is almost same. Just remove the semicolon at the end)

            var matched = from table1 in dt1.AsEnumerable()
                          join table2 in dt2.AsEnumerable() on table1.Field<String>("User") equals table2.Field<String>("User")
                          where table1.Field<String>("Client") == table2.Field<String>("Client") && table1.Field<string>("Date") == table2.Field<string>("Date")
                          select table1;

            var unmatched = from table1 in dt1.AsEnumerable()
                                           where !matched.Contains(table1)
                                           select table1;

Output:
image

Regards,
Karthik Byggari

4 Likes

if: row(“User”).tostring=row1(“User”).tostring and row(“Client”).tostring=row1(“Client”).tostring and row(“Date”).tostring=row1(“Date”).tostring

@rjonasson
Component for Two Datatable Column Match and Get The Matched Records.xaml (5.9 KB)
Above file will give matched records ie success table

Update assign stage to get non matched item.
If you are still not getting it, let me know.

Regards,
Aditya

Thanks so much, looks pretty straightforward! How do you apply a LINQ query? Do you use Assign?

Meaning that “var matched” falls into the left side of the assign activity and everything until “select table1;” goes into the right hand side?

So in that sense there would be two assign activities:

  1. var matched = from table1 in dt1.AsEnumerable()
    join table2 in dt2.AsEnumerable() on table1.Field(“User”) equals table2.Field(“User”)
    where table1.Field(“Client”) == table2.Field(“Client”) && table1.Field(“Date”) == table2.Field(“Date”)
    select table1

  2. var unmatched = from table1 in dt1.AsEnumerable()
    where !matched.Contains(table1)
    select table1

Another issue are the “table1” and “table2” fields. Are these references to a variable?

Thanks again!

Hi Rahatadi, really appreciate the help! I’ve tried applying the following logic but it says that my column name “Client” (first instance in code) doesn’t belong to the table.

I’m trying to apply assign:
MatchedData = Table1.AsEnumerable().Where(function(row) Table2.AsEnumerable().Select(function( r) r.Field(Of Int32)(“Client”.ToString)).Any(function(x) x = row.Field(Of Int32)(“Client”.ToString))).CopyToDataTable()

The above code would however be lacking two additional criteria, i.e. User=User & Date=Date , How would these be added to the code?

Do you have any idea of what the problem is?

I have managed to use the following assign activity (intended for removing timestamp from date) , where referencing one of the columns, so I wonder what the problem might be:

row.Item(“Date”) = row.Item(“Date”).ToString.Remove(10)

Sincerely,
Reynir

@rjonasson

Thanks for response…
Can you please upload your two files that you want to compare?

This may help you

Hi again,

Here is the dummy data, unfortunately can’t disclose the underlying data.

However as said before I need to match the data from Sheet1 with Sheet2, so for each row (1) When in Sheet1 the Activity is “Open” AND (2) the Dates are equal to the dates from Sheet2 then see if User=UserDT2 AND Client=ClientDT2 AND Date=DateDT2. If so then write “User”, “Client” and “Date” to Matched table, otherwise write “User”, “Client” and “Date” to Unmatched table

DummyData-MatchingTables.xlsx (11.2 KB)

Hope I’m being clear!

@rjonasson

please check following code.DummyData-MatchingTables.xlsx (11.2 KB)

matchrecords.xaml (9.5 KB)

this is what you want ?

Regards,
Aditya

please close topic by marking most useful reply as solution

1 Like

Hi again,

Thanks a lot! I’ve now managed to sort out most of what I was trying to accomplish. Although I’m not quite sure how to apply the additional filter e.g. that it only matches if the user from both rows matches in addition to the client.

I’m trying something like:
In_DataTable1.AsEnumerable().Where(Function(row) In_DataTable2.AsEnumerable().Select(Function® r.Field(Of Int32)(In_DT2_ColName_To_Match.ToString + In_DT2_ColName2_To_Match.ToString)).Any(Function(x) x = row.Field(Of Int32)(In_DT1_ColName_To_Match.ToString + In_DT1_ColName2_To_Match.ToString))).CopyToDataTable()

But this is removing all rows but should be giving me results for the non matched rows

Any ideas on what could be the problem here?

Again, i’m really appreciative of your time, thank you so much!

Sincerely,
Reynir

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