Filtering Between Duplicates

Help Activities
, , , ,
1

Hi!

I’m having issues with trying to filter between duplicates based on certain criteria. Apologies, new users can’t upload files. Here is a google drive link to the example Excel.

The Excel sheet “Example1” displays a table with the listed values.
Criterias:
The rows with no duplicates stay as is.
Duplicate rows are sorted by column “Level” looking for the highest level among the duplicates.
If there is only one highest level for a User, it shall be kept and the rest are removed.
After the “Level” is sorted, the duplicate with the earliest “Date” is the one that is kept, the rest are to be removed.

In the Excel sheet “Result” is the expected result from the bot.

Please let me know of any ideas or solutions to this!

Thank you for taking the time to help!

1 Like
2

Hi @pathfinder_mw
→ Read Range Workbook
Output-> dt
→ Use the below query in Assign activity:

Assign-> dt_Output= dt.Clone()

Assign-> dt_Output= (From row In dt.AsEnumerable()
                       Group By ID = row.Field(Of String)("ID"),
                                Name = row.Field(Of String)("Name")
                       Into Group
                       Let maxLevel = Group.Max(Function(r) If(Integer.TryParse(r.Field(Of String)("Level"), 0), Convert.ToInt32(r.Field(Of String)("Level")), 0))
                       From g In Group
                       Where If(Integer.TryParse(g.Field(Of String)("Level"), 0), Convert.ToInt32(g.Field(Of String)("Level")), 0) = maxLevel
                       Order By maxLevel, DateTime.Parse(g.Field(Of String)("Date"))
                       Select g).GroupBy(Function(r) r("ID")).Select(Function(g) g.First()).CopyToDataTable()

→ Write Range Workbook
image
Input.xlsx (9.7 KB)

image
image835×636 28.4 KB

Sequence2.xaml (9.4 KB)

Regards

1 Like
3

Hi,

FYI, another approach:

dt = dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("ID").ToString,r("Name").ToString)).Select(Function(g) dt.Clone.LoadDataRow(g.OrderByDescending(Function(r) CInt(r("Level"))).ThenBy(Function(r) CDate(r("Date"))).First.ItemArray,False)).CopyToDataTable

image
image735×385 16.7 KB

Sample
Sample20240205-1.zip (9.9 KB)

Regards,

1 Like
4

Hi @pathfinder_mw

If you find the solution for your query please mark the post as solution to close the loop or if you have any questions, I’m happy to help.

Happy Automation!!

1 Like
5

Thanks! @Parvathy and @Yoichi for the ideas and references!

I have tried out both, @Yoichi one seems to work as I am getting a “Sources has no data rows” error from @Parvathy solution. Thank you for the time! I am trying to implement it to the master data!

Btw, @Yoichi if it does not trouble you. will it be possible to break down the code that is written? The code as is, I have trouble trying to understand what functions and activities are being called to achieve the result.

Once again thank you guys for the help! Much appreciated!

6 
dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("ID").ToString,r("Name").ToString)).Select(Function(g) dt.Clone.LoadDataRow(g.OrderByDescending(Function(r) CInt(r("Level"))).ThenBy(Function(r) CDate(r("Date"))).First.ItemArray,False)).CopyToDataTable

The following first part returns IEnumerable<IGrouping<Tuple<string,string>,IEnumerable<datarow>>>. It’s like dictionary which is pair of ID and Name as Key and multiple datarow which is same row with the pair (key) as Value.

    dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("ID").ToString,r("Name").ToString))

Next, create datatable using the above grouping. Now, we need a datarow which is top in descending order of “Level” column then top in ascending order of “Date” column. So OrderByDescending and ThenBy is used.
And get object array using ItemArray. It’s necessary in later LoadDataRow method

g.OrderByDescending(Function(r) CInt(r("Level"))).ThenBy(Function(r) CDate(r("Date"))).First.ItemArray

Finally, we create datatable from list of the above datarow using the following LoadDataRow and CopyToDataTable

.Select(Function(g) dt.Clone.LoadDataRow(g.OrderByDescending(Function(r) CInt(r("Level"))).ThenBy(Function(r) CDate(r("Date"))).First.ItemArray,False)).CopyToDataTable

Hope this helps you.

Regards,

1 Like
7

Thank you so much! Appreciate it!

1 Like
8

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