Filter DT with repeated data, leaving the most recent ones

Help Something Else
, , ,
1

Hi, I need help.

I have a datatable that has 6 columns: “Process”, “State”, “Folder process”, “Start time”, “End time”, “Host name (VM)”.

What I need is to stay with the most recent data that has the same “Process”, “State”, “Folder process”. The column I have is “Start time” and “End time”, the idea would be to use one of the two and filter so that it leaves me the most recent data.

In the image I show you an example, so that you better understand what I mean:

imagen

I tried to do it with Linq, but I have very little knowledge so it didn’t work and it’s wrong, I still put the code. There could be many lines, that’s why I use Linq.

out_dtDataFilteredJobs =

(From row In out_dtAllDataJobs.AsEnumerable                                                                                                    
  Group row By k=row("Process").ToString.Trim,  k2=row("State").ToString.Trim, k3= row("Folder process").ToString.Trim ,k4=row("End time").ToString.Trim, k5=row("Host name (VM)")
  Into grp=Group
  Let startDate = grp.OrderBy(Function (r) DateTime.ParseExact(r("Start time").toString.Trim,"dd/MM/yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture)).First
  Select out_dtDataFilteredJobs.Rows.Add({k, k2, k3, startDate, k4, k5})
).CopyToDataTable

Thx!

2

Hi,

Can you try the following expression?

(From row In out_dtAllDataJobs.AsEnumerable                                                                                                    
  Group row By k=row("Process").ToString.Trim,  k2=row("State").ToString.Trim, k3= row("Folder process").ToString.Trim , k5=row("Host name (VM)")
  Into grp=Group
  Select grp.OrderByDescending(Function (r) DateTime.ParseExact(r("Start time").toString.Trim,"d/M/yyyy H:m",System.Globalization.CultureInfo.InvariantCulture)).First()
).CopyToDataTable

Sample
Sample20230929-1L.zip (10.1 KB)

If DateTime parse error occurs, the following may work in datatime.parse part.

CDate(r("Start time").toString.Trim)

Regards,

1 Like
3

@Maite_Escalada

Please try this …

Dt.AsEnumerable.GroupBy(function(x) x("Process").ToString+ x("State").ToString+ x("Folder process").ToString+ x("Host name (VM)").ToString).Select(function(x) x.OrderByDescending(function(y) Cdate(y("Start time").ToString)).First()).CopyToDataTable

Hope this helps

Cheers

1 Like
4

Thank you very much, it worked perfectly.
I only had to change the date format, since I needed it to be “dd/MM/yyyy HH:mm:ss”

5

Thanks, it worked fine, I got the same result as the other comment. Only I had to modify the date part:

out_dtAllDataJobs.AsEnumerable.GroupBy(Function(x) x("Process").ToString+ x("State").ToString+ x("Folder process").ToString+ x("Host name (VM)").ToString).Select (Function(x) x.OrderByDescending(Function (r) DateTime.ParseExact(r("Start time").toString.Trim,"dd/MM/yyyy HH:mm:ss",System.Globalization.CultureInfo.InvariantCulture) ).First()).CopyToDataTable

1 Like
6

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