Get distinct rows from Datatable from Multiple columns

Help
1

Hi,
I want to compare two datatables and get distinct values based on multiple columns. Suppose i have dt1 with following data
|Trailer_No|Creation_Date|Ship_To|Status|
|RPA_1|03/01/2019 00:00:00|ABC|Failed|
|YU87687|01/09/2019 00:00:00|ABC|Failed|
|987|10/25/2018 00:00:00|ABC|Failed|

and second dt2 with following data
|Trailer_No|Creation_Date|Ship_To|Status|
|RPA_1|03/01/2019 00:00:00|ABC|In Process|
|YU87687|01/09/2019 00:00:00|ABC|In Process|
|987|10/25/2018 00:00:00|ABC|In Process|
|TEST25|07/25/2018 00:00:00|ABC|In Process|
|TEST25_1|07/25/2018 00:00:00|ABC|In Process|

now the first three rows in dt2 are duplicate only the status is different. I want those three rows removed and get rows from dt1 with ‘failed’ status and new rows from dt2 into dt3,

Any help is appreciated. TIA

3

Try Implementing the steps

  1. Read both the excels and store in two data tables.
  2. As you want all the values from first table, write first table to a new excel
  3. Use for each row to iterate through the rows of second column Trailer_No and use another for each row inside to compare with the first table Trailer_No.
  4. Use if condition to check whether the values are matched or not ( whatever conditions you want)
  5. In then condition, use write cell activity to write in the third excel if the values matched or vice-versa.

Hope this helps.

4

Hi, I will try your steps and update you. Thanks for the reply

5

@ajinkya2704
If it is possible just try the code like this,

Create two datatable and assign the above mentioned values and using assign activity

dtTest3 = (From x In ((From y In dtTest1.AsEnumerable() Select y).Union(From z In
dtTest2.AsEnumerable() Select z))
Group By Y = New With { Key.Id=x(“ID”), Key.Name=x(“Name”)} Into GroupA= Group
Select GroupA(0)).Toarray().copytodatatable()

Thanks,
Pradeep Sridharan

6

@Pradeep thanks for the reply, let me explain what i am currently doing, I am merging the two datatable into a third one, then i am doing Datatable.DefaultView.ToTable(True,“Trailer_No”,“Creation_Date”,“Ship_To”,“Status”)
This is also not removing duplicates, am i doing anything wrong here!

Thanks,
AJ

7

@ajinkya2704

Please find the below xaml

Main.xaml (18.8 KB)

Thanks,

Pradeep Sridharan

1 Like
8

@Pradeep I tested your code and integrated in my workflow, it working as expected, appreciate your help :slight_smile:

Thanks,
AJ

1 Like
9

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