How to find latest time

Help Activities
, ,
1

If I want to find the latest time if all dates are same then how to do that?

I do have linq query for finding max date, but looking for max time

(From row In dtInput
Group By id=row(0) Into grp = Group Let md=grp.Max(Function (d) d(1)) Select dtMax.Rows.Add({id,If(md.ToString.Trim.Equals(String.Empty),“”,CDate(md.Tostring).ToString(“MM/dd/yyyyy”))})).CopyToDataTable

Following are three columns

Username Login Date Login Time
Gary.jones@abc.com 3/4/23 12:15 PM
Gary.jones@abc.com 3/4/23 12:30 PM
Gary.jones@abc.com 3/4/23 12:45 PM
Gary.jones@abc.com 3/4/23 1:30 PM
John.keller@abc.com 3/5/23 9:45 AM
John.keller@abc.com 3/5/23 10:00 AM
Lisa.pine@abc.com 3/6/23 1:15 PM
Lisa.pine@abc.com 3/6/23 1:30 PM
Lisa.pine@abc.com 3/6/23 1:45 PM
Lisa.pine@abc.com 3/6/23 2:00 PM
Karen.byers@abc.com 3/6/23 2:15 PM
Karen.byers@abc.com 3/6/23 2:30 PM
Karen.byers@abc.com 3/6/23 2:45 PM
Karen.byers@abc.com 3/6/23 3:00 PM
Kyle.simpson@abc.com 3/6/23 3:15 PM
Kyle.simpson@abc.com 3/6/23 3:30 PM
Kyle.simpson@abc.com 3/6/23 3:45 PM
2

@khooshbu.jani

Try this

(From row In dtInput Group By id=row(0) Into grp = Group Let md=grp.Max(Function (d) d(1)) mt = grp.Max(Function(x) Cdate(If(x(1).ToString.Trim.Equals(String.Empty),"01/01/1900",CDate(x(2).Tostring).ToString("MM/dd/yyyy")) + If(x(2).ToString.Trim.Equals(String.Empty),"00:00:00 AM",Date.ParseExact(x(2).Tostring,"hh:mm tt",System.Globalization.CultureInfo.InvariantCulture).ToString("hh:mm:ss tt")))) Select dtMax.Rows.Add({id,If(md.ToString.Trim.Equals(String.Empty),“”,CDate(md.Tostring).ToString("MM/dd/yyyyy")),cdate(mt).ToString("hh:mm tt")})).CopyToDataTable

Please change the time format if its different…hh:mm tt is what is the format for 11:12 AM

Hope this helps

Cheers

3

Hi @khooshbu.jani

Please try the below query:

(From row In dtInput
Group By id=row(0) Into grp = Group
Let maxDateTime = grp.Max(Function(d) CDate(d(1).ToString.Trim & " " & d(2).ToString.Trim))
Select dtMax.Rows.Add({id, If(maxDateTime.ToString.Trim.Equals(String.Empty), “”, maxDateTime.ToString(“MM/dd/yyyyy hh:mm:ss tt”))})
).CopyToDataTable

Hope this helps,
Best Regards.

4

Hi,

I tried it, but getting following error:

Test.xaml: Compiler error(s) encountered processing expression “(From row In dtInput Group By id=row(0) Into grp = Group Let md=grp.Max(Function (d) d(1)) mt = grp.Max(Function(x) Cdate(If(x(1).ToString.Trim.Equals(String.Empty),“01/01/1900”,CDate(x(2).Tostring).ToString(“MM/dd/yyyy”)) + If(x(2).ToString.Trim.Equals(String.Empty),“00:00:00 AM”,Date.ParseExact(x(2).Tostring,“hh:mm tt”,System.Globalization.CultureInfo.InvariantCulture).ToString(“hh:mm:ss tt”)))) Select dtMax.Rows.Add({id,If(md.ToString.Trim.Equals(String.Empty),“”,CDate(md.Tostring).ToString(“MM/dd/yyyyy”)),cdate(mt).ToString(“hh:mm tt”)})).CopyToDataTable”.(2) : error BC30198: ‘)’ expected.
(2) : error BC30198: ‘)’ expected.
(2) : error BC30512: Option Strict On disallows implicit conversions from ‘IEnumerable(Of <anonymous type: Key id As Object, Key grp As IEnumerable(Of DataRow), Key md As Object>)’ to ‘DataTable’.

No " ) " is missing. I checked it.

5

@khooshbu.jani

I missed a let before mt

(From row In dtInput Group By id=row(0) Into grp = Group Let md=grp.Max(Function (d) d(1)) Let mt = grp.Max(Function(x) Cdate(If(x(1).ToString.Trim.Equals(String.Empty),"01/01/1900",CDate(x(2).Tostring).ToString("MM/dd/yyyy")) + If(x(2).ToString.Trim.Equals(String.Empty),"00:00:00 AM",Date.ParseExact(x(2).Tostring,"hh:mm tt",System.Globalization.CultureInfo.InvariantCulture).ToString("hh:mm:ss tt")))) Select dtMax.Rows.Add({id,If(md.ToString.Trim.Equals(String.Empty),“”,CDate(md.Tostring).ToString("MM/dd/yyyyy")),cdate(mt).ToString("hh:mm tt")})).CopyToDataTable

Cheers

6 
(From d In dtInput.AsEnumerable
Group d By k=d(0).toString.Trim Into grp = Group 
Let go = grp.OrderBy(Function (x) CDate(x(1).toString.Trim).Date).ThenBy(Function (y) CDate(y(2).ToString.Trim).TimeOfDay)
Select r = go.Last()).CopyToDataTable

grafik
grafik891×142 5.18 KB

7

@Anil_G

I replaced the above script and compiled the program, still getting this error:

Assign: Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled.

8

@khooshbu.jani

I guess you copy pasted it…replace the inverted comma…in the code…check them properly

Cheers

9

@ Peter

This has the same error of
Assign: Expression Activity type ‘VisualBasicValue`1’ requires compilation in order to run. Please ensure that the workflow has been compiled

10

retype any double Quotes and ensure that these are of " (comes often from copy and paste issues)
check the fix:

CDate(y(2).To

grafik
grafik749×220 10.2 KB

11

Same error for your query too.

assign: expression activity type ‘visualbasicvalue`1’ requires compilation in order to run. please ensure that the workflow has been compiled

12

@ Anil
I typed “” but it is giving me conversion error: Assign Conversion from string “01/01/000112:12:15:00 PM” to type ‘Date’ is not valid.

For simplicity, i removed time column which was separate and formate cell with “3/4/2023 8:01:00 AM”
Again when I have date and time written, why it is taking in 01/01/000112:12:15:00?
It has to check against each user last date and time logged in.

I know it is taking long, but I try to refer UiPath Forum before putting here.
:
!:image|682x103:

13

Please find excel sheet for your reference on which I am working

Failed_Login_Attempt_Spreadsheet.xlsx (16.0 KB)

14

@khooshbu.jani

Please check this modified as per the data provided

(From row In dt Group By id=row(0) Into grp = Group Let md=grp.Max(Function (d) d(1)) Let mt = grp.Max(Function(x) Cdate(If(x(1).ToString.Trim.Equals(String.Empty),"12/30/1899 ",CDate(x(1).Tostring).ToString("MM/dd/yyyy ")) + If(x(2).ToString.Trim.Equals(String.Empty),"00:00:00",Cdate(x(2).Tostring).ToString("hh:mm:ss")))) Select dt1.Rows.Add({id,If(md.ToString.Trim.Equals(String.Empty),"",CDate(md.Tostring).ToString("MM/dd/yyyyy")),cdate(mt).ToString("hh:mm tt")})).CopyToDataTable

image

blank rows are also grouped…can remove them using a filter datatable

cheers

15

Thank you for the help.
It is giving me error of dt and dt1 is either not declared or not in the current scope. I see dt is declared, still it gives me the error.
I changed dt and dt1 to dtmax. so now it is giving the source contains no datarows. I do have data, Also I checked with file path. It is opening the correct file.

16

Source do contain datarows

17

@khooshbu.jani

You have to change dt to dtinput and dt1 to dtmax as per your file

I hope your input table is dtinput

Cheers

18

I was about to write that. I got it. I think i have gone crazy and not applying my mind.

But I am not getting the correct output.
(1) As per the Data, for Gary, It should give me latest time as 1.30pm, but gives me 12.45 pm

|Gary.jones@abc.com|3/4/2023|12:15 PM|
|Gary.jones@abc.com|3/4/2023|12:30 PM|
|Gary.jones@abc.com|3/4/2023|12:45 PM|
|Gary.jones@abc.com|3/4/2023|1:30 PM|

(2) and for Lisa gives 2 AM instead of 2 PM

|Lisa.pine@abc.com|3/6/2023|1:15 PM|
|Lisa.pine@abc.com|3/6/2023|1:30 PM|
|Lisa.pine@abc.com|3/6/2023|1:45 PM|
|Lisa.pine@abc.com|3/6/2023|2:00 PM|

(3) for all other it is giving output in AM instead of PM

19

@khooshbu.jani

Try including "hh:mm:ss tt" in the time field and Change 00:00:00 AM

And also i guess i included an extra y in the dtmax select statement…remove it make it four y’s only

Cheers

20

No Anil, it still gives me the same error. I noticed yyyyy part and removed one y. After your instruction i add ss part and AM part. still not working. if this is too complicated can we use the Login Date/Time column, which has a format of 3/4/2023 8:01:00 AM for simplicity?