How to filter a recent date from date column if some other column as duplicate value

Help
,
1

Hi Team ,
I had a scenario like have to remove the duplicate row and want to keep the first one data table . can someone please help on this 2020-12-31 12_15_06-Book1 - Excel

2

@vvaidya @Madhavi

3

Have you tested the Remove Duplicate Rows activity?

4

Yes but it is working . as I need to keep first one and ignore all below duplicate rows

5

image
I just dont know why it is not removed 1 in the table

6

Hello @Vijay_RPA,

Try this,

First copy dtData.clone() in new datatable varaiable, next use assign and in To the dataTable Variable and in value try below query’s.

(From d In dtData.AsEnumerable
Group d By k1=d(“Number”).toString.trim Into grp=Group
Let oldest = grp.OrderBy(Function (r) DateTime.ParseExact(r(“date”).toString.Trim,“MM-dd-yyyy hh:mm tt”, CultureInfo.InvariantCulture)).Last
Select dtResult.Rows.Add(oldest.ItemArray)).CopyToDataTable

or

(From d In dtData.AsEnumerable
Group d By k1=d(“Number”).toString.trim Into grp=Group
Let oldest = grp.OrderBy(Function (r) DateTime.ParseExact(r(“date”).toString.Trim,“MM-dd-yyyy hh:mm tt”, CultureInfo.InvariantCulture)).First
Select dtResult.Rows.Add(oldest.ItemArray)).CopyToDataTable

7

Thanks AKshay will try it now

8

You have two “1” rows because the time differs. 03:05 PM vs. 02:04 PM. The activity only considers it a duplicate if all column values match.

9

image
image1007Ă—352 22.1 KB

Seems to be syntax error @Akshay07 :frowning:

10

Hi @Vijay_RPA,

Please try ths,

(From d In dt1.AsEnumerable
Group d By k1=d(“Number”).toString.trim Into grp=Group
Let oldest = grp.OrderBy(Function (r) DateTime.ParseExact(r(“Date”).toString.Trim,“MM-dd-yyyy hh:mm tt”,System.Globalization.CultureInfo.InvariantCulture)).First
Select dt1.Rows.Add(oldest.ItemArray)).CopyToDataTable

image
image1089Ă—509 12.5 KB

1 Like
11

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