Datatable comparison using multiple columns

Hi,
I have been trying to do a datatable comparison to first check if the value of the cells in a column ‘ID’ match and if they do proceed to check if the cell value of the column ‘Name’ in one datatable is present in the other if not append a remark to a fresh datatable after which it checks for the cell value of the column ‘Amount’ credited if they are the same, less credited or over credited and also gives a remark in the new datatable.

So far i have been able to achieve ‘ID’ and ‘Amount’ comparison with this workflow Testuse.xaml (24.3 KB) but yet to achieve the name comparison with these data schedule received.xlsx (9.7 KB) Schedule Sent.xlsx (9.5 KB)

Here’s the new datatable format.

Thanks.

please forum leaders i need your contribution @Jan_Brian_Despi @Lakshay_Verma @Lahiru.Fernando @Palaniyappan

2 Likes

@John_Dara
find some starter Help, aligned on your Excels
Testuse_V2.xaml (22.1 KB)

Kindly note:

  • It is just a starter Help
  • it is setup in a conversative way, just to let more easy to enter for the next implementation steps

For Datatable Join it is important what is dt1, dt2

Please avoid Topic duplicates like this one:

5 Likes

@ppr Thanks for this would check it out now.

1 Like

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

  • 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

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