How to group excel data table?

Hi,

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

image

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

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

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

image

can it be processed maybe split or filter?

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

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

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 :slight_smile: thank you !

Hi,

I tried your solution but I go this error instead :

image

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

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

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

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

Anyway …Is it working Fine For you ?

Nope it’s not, the error is still there

ScreenShot ?

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

Ok …I edited Query Check it once.

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

DT2 For store the Final Output

1 Like

Ok thanks for the help Mr. Gangadhar!

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