Remove duplicate rows if a column value matches

Help Studio
, ,
1

Hi all,
How can I remove duplicate rows only if column Time Off Quantity = 1 or -1 and retain all the other remaining columns as it is
Can this be done using linq query?

Input dt:

Time Off Type Time Off Date Time Off Quantity
Rest Day 04/28/2023 1
Annual Vacation 04/11/2023 1
Annual Vacation 04/11/2023 1
Annual Vacation 04/13/2023 0.5
Annual Vacation 04/13/2023 0.5
Annual Vacation 04/17/2023 -0.5
Annual Vacation 04/21/2023 -1
Annual Vacation 04/21/2023 -1
Annual Vacation 04/24/2023 1

Output dt:

Time Off Type Time Off Date Time Off Quantity
Rest Day 04/28/2023 1
Annual Vacation 04/11/2023 1
Annual Vacation 04/13/2023 0.5
Annual Vacation 04/13/2023 0.5
Annual Vacation 04/17/2023 -0.5
Annual Vacation 04/21/2023 -1
Annual Vacation 04/24/2023 1
2

Hi @niro

Try this-

var filteredData = yourData.GroupBy(row => new {row.Column1, row.Column2, row.Column3, row.Column4})
.Where(group => group.Count() == 1 || (group.First().TimeOffQuantity != 1 && group.First().TimeOffQuantity != -1))
.Select(group => group.First());

3

@niro
we assume VB.Net code is needed, right?

4

find some starter help here

dtResult =

(From d In yourDataTableVar.AsEnumerable
Group d By k1=d(0).toString.Trim, d(1).toString.Trim Into grp=Group
Let setNonAbsOne = grp.Where(Function (f1) Not Math.Abs(CDbl(f1(2).toString.Trim)).Equals(1.0))
Let setAbsOne = grp.Except(setNonAbsOne, DataRowComparer.Default).Take(1)
From g In setNonAbsOne.Concat(setAbsOne)
Order By yourDataTableVar.Rows.IndexOf(g)
Select r = g).CopyToDataTable

Keep in mind:

:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

:ambulance: :sos: [FirstAid] Datatable: Debug & Analysis invalid DateTime Strings / String to DateTime Parsing Issues - News / Tutorials - UiPath Community Forum

5

thank you. i actually have more columns in the actual datatable and i’d need to group by 3 columns. what is wrong with the below query? i’m getting error “Range variable Trim is already declared”
Group d By k1=d(0).toString.Trim, d(1).toString.Trim, d(5).toString.Trim Into grp=Group

there’s also another issue because the query is checking Math.Abs(1), so it removes both 1 and -1 which is not duplicate as below. it should only remove when there’s another same value of 1/-1 found for the same date

|Annual Vacation|04/21/2023|1|
|Annual Vacation|04/21/2023|-1|

6

hi @Nitya1 i believe i need to use invoke code right? i’m not allowed to use invoke code activity in my organization

7

Hi @niro

you can use remove duplicate activity under datatables in UiPath

8

remove duplicates will remove all duplicates, i only need to remove duplicates if column Time Off Quantity = 1 or -1 and remain the other rows. i’ve already stated it above

9

can you split into 2 with filter the DataTable with values -1/1 then remove duplicates and merge the resulting datatable other other datatable

1 Like
10

You don’t need invoke code activity , write this code in assign activity.

11

Better to tell all complete requirements and needs at the begin

we didn’t mention such an usage of Math.Abs

we would also use k2=…, k3=…

Also have a look here:

we can also combine LINQ and NON-Linq approaches