Datatable comparison using multiple columns

Help Studio
5

Hi @John_Dara

Following the attached workflow created using the Linq queries with minimal activities.

  • Used the received and sent excel attached by you in your question
  • Used Join datatable with full outer join to join receive and sent files
  • Used the following linq query to update the both amount_remarks and name_remarks at a shot

(From d In dtNewBuild.AsEnumerable
Let amount_remarks = If( CDbl(d(“Amount_Sent”))=0, “Amount not sent”, If(CDbl(d(“Amount_Credited”))=0, “Amount not credited”, If(CDbl(d(“Amount_Sent”))>CDbl(d(“Amount_Credited”)), “Credited less”, If(CDbl(d(“Amount_Sent”))<CDbl(d(“Amount_Credited”)), “Over Credited”, “Same” ))))
Let name_remarks = If(d(“Name_Sent”).tostring.trim.tolower.contains(d(“Name_Credited”).tostring.trim.tolower) Or d(“Name_Credited”).tostring.trim.tolower.contains(d(“Name_Sent”).tostring.trim.tolower), “”, “Wrong Name”)
Select dtResult.Rows.Add(New Object(){d(0),d(1),d(2),d(3),d(4),d(5),name_remarks,amount_remarks})).CopyToDataTable

  • After the execution, you will see the output.xlsx generated with the result which looks like this.

Output screenshot

image
image1017×304 13.2 KB

  • There is a little modification for the non matched ID’s (last 4 records from the output screen shot) from what you were expecting but I hope you might consider this.

Note:
You can also use SQL queries in execute query activity to achieve the output.

SQL and LINQ are powerful hence faster and uses fewer activites giving the best performance.

Please find the attached zip file to run the XAML file.
matchind_datatable.zip (24.6 KB)

3 Likes