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
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.
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;
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:
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
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?
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:
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
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.
hacky
ppr
Abang_Jamuri_Abang_Shoker
AkshaySandhu
sharazkm32
ashokkarale
tolga.kamci
Anirudh_Tugawe
sathish_moorthy
marita