I’m looking for help with regards to this data table sorting which I’m having issues with.
Data Table .xlsx (10.7 KB)
Attached is the Excel which contains the problem stated and the ideal end result.
To sum it up, I need the data table to be filtered based on a few rules, and I hope that this can be completed with vb.net but it is not my strong suit.
I appreciate any help and thank you for your time!
End Result:
Cheers!
Hi,
How about the following sample?
dt.AsEnumerable.GroupBy(Function(r) r("Identity").ToString).Select(Function(g) g.OrderByDescending(Function(r) if(Int32.TryParse(r("Level").ToString,New Int32),Cint(r("Level").ToString),Int32.MaxValue)).ThenBy(Function(r) DateTime.Parse(r("Date").ToString)).First()).CopyToDataTable()
Sample
Sample20240226-1.zip (11.0 KB)
Regards,
Hi Yoichi!
Thanks for the solution! Most of the result looks great but for the results of “Annie” and “Paul” the filter seems to take the highest level’s date, would it be possible to just take the first line of the record as it coincides with Scenario 2 and should output the first line of the personnel’s record respectively
Once again thanks for your help!
HI,
All right. It seems the condition is a little bit complicated. How about the following?
Sample
Sample20240226-1 (2).zip (11.9 KB)
Regards,
Thanks Yoichi!
Sorry for the late reply, have been busy! I have tried implementing the code however Add Data Row seems to give the error:
Do you have any idea on this as the string is indeed a date.
Thanks!
Hi,
If your datetime is always “dd/MM/yyyy” style, the following expression will work.
listRow.OrderBy(Function(r) DateTime.ParseExact(r("Date").ToString(),"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture)).FIrst().ItemArray
Regards,
Hi,
There might be non-datetime string including empty. So can you check if all the data is DateTime or not? If not, can you share how to handle it (for example ignore it) ?
Regards,
Hi!
I have checked through the excel and they are all dates. The add data row seems to be unable to proceed due to this error.
Thanks!
Hi,
All right. The following expression filters out non-datetime data row, then extract target data. Can you try this?
listRow.Where(Function(r) DateTime.TryParseExact(r("Date").ToString(),"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture,System.Globalization.DateTimeStyles.None,New DateTime)).OrderBy(Function(r) DateTime.ParseExact(r("Date").ToString(),"dd/MM/yyyy",System.Globalization.CultureInfo.InvariantCulture)).FIrst().ItemArray
Regards,
Hi!
I have applied the code but a new error has surfaced.
My apologies for the trouble that I have caused with this, thank you for your time!
Hi,
To isolate cause, can you try to set break point at the AddDataRow, then check content of Date column in the datatable at Locals panel when stop there in debug mode? (dd/MM/yyyy style, any other style or mixed)
Regards,
Hi!
I checked the excel and cleaned up again. I tried running the original line of code with CDate, however it seems to be taking the header as a string? Maybe I could work from the original line of code if I could circumvent this issue?
Also, with running the breakpoint and checking locals, it just states that the sequence does not existing.
Thank you!
HI,
First, can you check if AddHeaders option of ReadRange activity is True?
If it’s already true, can you share your settings of ReadRange and your worksheet?
Regards,
Can you also share which address “Start Date” exists in your sheet?
It seems strange…
Can you check content of the datatable just after ReadRange activity? (give it a try to set Breakpoint just after ReadRange then debug run)
Hi!
Sorry for the late reply once again. I played around with it and worked with the code:
ListRow.OrderBy(Function(r) r("Date")).First().ItemArray
The resulting output is correct and it seems to be an issue with the software as I am using an older version of UiPath.
Thank you very much for the help Yoichi! Appreciate it! Have a nice week ahead!
Cheers!
Edit: For future cases, encasing the code in a try catch could help circumvent the issue as well!
This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.