Groupby and find the highest value

Build
, ,
#1

I am having an excel attached Highest.xlsx (9.7 KB)
where the input sheet image
The sheet contain duplicate loom ID details and i want only those rows having highest “Work” value.

For example - “234” having the highest Work value 2345, so keep only that row and same for other loom id’s.

and output sheet image

Please help

#2

Hi.
dict = dt.AsEnumerable().GroupBy(
Function(row) row.Field(Of Double)("Loom ID").ToString).ToDictionary(
Function(g) g.Key, Function(g) g.AsEnumerable.Max(Function (row) CInt(row("Work").ToString)).ToString)

And make sure the column name in excel is ‘Work’ not 'Work '.
dict is a Dictionary of String, String in my example. If you need other datatypes, feel free to ask

#3

@Yurii_Horobets after applying the assign activity (as shared by you), how would i get the required output in datatable form …can you please help ?

I am sharing the file here Main.xaml (6.5 KB)

#4

Do you need every row with max Work or only one?
LoomID/Work
10/1123
11/456
11/457
11/457
The output should be
LoomID/Work
10/1123
11/457
or
LoomID/Work
10/1123
11/457
11/457

#5

@Yurii_Horobets i only want one row with max output …as you explained in first condition
i.e. output should be
LoomID/Work
10/1123
11/457

but the output should be in the format shared in the excel output sheet

#6

Main.xaml (10.4 KB)
Build Data Table (for output)
dict = dt.AsEnumerable().GroupBy(
Function(row) row.Field(Of Double)("Loom ID").ToString).ToDictionary(
Function(g) g.Key, Function(g) g.AsEnumerable.Max(Function (row) CInt(row("Work").ToString)).ToString)
For Each item in dict {
tempdatarow = dt.AsEnumerable.Where(Function (row) row(“Loom ID”).ToString.Equals(item.Key) and row(“Work”).ToString.Equals(item.Value)).CopyToDataTable.Rows(0)
Add Data Row: tempdatarow to outputDT

}

#7

Thanks a lot @Yurii_Horobets but when i am using the code on my real data it is showing the error

“Assign: Specified cast is not valid.” - on the dict assign activity.

In my real data Loom ID is of string type and Work is of Double type

Can you suggest any changes…?

#8

Yep.
Main.xaml (10.7 KB)

#9

@Yurii_Horobets Thanks for your support but the error still exist …is there any other thing i am missing …

My actual data has some additional columns other than shared one’s.

#10

Feel free to adjust datatables to your format. Solution should not crash

#11

@Yurii_Horobets thanks for help

I got the situation why error is coming

In Loom ID column …my dummy values are like “123”,“13”, etc. all numeric form but my actual values are like “L0123”,“M2323”.

When i replaced the values with numeric one’s the workflow worked but in actual loom id values it is giving the above mentioned error.

Can you suggest what should i change now ?

Thanks for help in advance

#12

Main.xaml (10.6 KB)

1 Like
#13

Thanks for the solution @Yurii_Horobets…it worked

#14

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