How to group excel data table?

[Rhys18 (Rizky Albert Hartono)]

Hi,

I have a set of data like this from excel file with the expected output :

so I have a data that can be seen in the picture. My goal is I have to get the highest price of each car type.

How to do it in UiPath? The sequence is write from excel to another excel

[ppr (Peter)]

assign Acitvity
dtResult = yourOriginDataTableVar.Clone

Assign Activity
dtResult =

(from d in yourOriginDataTableVar.AsEnumerable
Group d by k=d("Car Type") into grp = Group
Let mx = grp.Max(Function (x) CInt(x("Price").toString.Trim))
Let ra = new Object(){k,mx}
Select r = dtResult.Rows.Add(ra)).CopyToDatable

[Rhys18 (Rizky Albert Hartono)]

This case is only with 2 columns only and we just process the data table.

Can it be done with a variable like string? Assuming the data is now like this

can it be processed maybe split or filter?

[ppr (Peter)]

@ppr

dtResult = yourOriginDataTableVar.Clone

prepare with e.g. build datatable activity an empty datatable within the two-column structure instead

[HowTo] Overview on different options for grouping data and processing the groups

This HowTo introduces on the different options for grouping data from a datatable in order to process the grouped data. Introduction Grouping data and processing the grouped data is a common scenario e.g. when the grouped data is to aggregate like summing up, find maximum, get the average or concatening items. Lets have a look on following data: [grafik] A possible scenario could be: Create a report containing following information: the region code the sum of CaseCount per RegionCode t…

[Gangadhar_Athili]

inputDT.Asenumerable.Groupby(Function(r) r(“car type”).Tostring).Select(Function(g) DT2.Rows.Add({g.key,g.Asenumerable.Max(Function(r) CInt(r(“Sold Price”)))})).copytodatatable

[Rhys18 (Rizky Albert Hartono)]

Hi,

I just notice you are using LinQ queries… Thanks for the explanation I will take it as a new knowledge since I am currently on deadline thank you !

[Rhys18 (Rizky Albert Hartono)]

Hi,

I tried your solution but I go this error instead :

‘Comma, ‘)’ or a valid expression continuation expected’

I tried to fix the brackets () and it’s still throwing the same error.

[Gangadhar_Athili]

At the End …After “}” You Need to keep only 2 “)” .

[Rhys18 (Rizky Albert Hartono)]

@Gangadhar_Athili

inputDT.Asenumerable.Groupby(Function(r) r(“car type”).Tostring).Select(Function(g) DT2.Rows.Add({g.key,g.Asenumerable.Max(Function(r) CInt(r(“Sold Price”))})).copytodatatable

Yes the first time I copy paste your code it’s throwing that error

[Gangadhar_Athili]

Anyway …Is it working Fine For you ?

[Rhys18 (Rizky Albert Hartono)]

Nope it’s not, the error is still there

[Gangadhar_Athili]

ScreenShot ?

[Rhys18 (Rizky Albert Hartono)]

Here is the code

dt_input_filtered_2.Asenumerable.Groupby(Function(r) r("Type").Tostring).Select(Function(g) DT2.Rows.Add({g.key,g.Asenumerable.Max(Function(r) CInt(r("Sold Price"))})).copytodatatable

[Gangadhar_Athili]

Ok …I edited Query Check it once.

[Rhys18 (Rizky Albert Hartono)]

Okay it’s not showing error anymore ! But I got a question to ask.

InputDT is the variable where it contains the Car Type and Sold Price

What is Dt2 for? Some kind of temp to store the Car Type and Sold Price

[Gangadhar_Athili]

DT2 For store the Final Output

[Rhys18 (Rizky Albert Hartono)]

Ok thanks for the help Mr. Gangadhar!