Group by duplicate ID and take the highest available amount from other columns

create a dt variable with assign activity

Thanks
ashwin S

1 Like

not working. i assigned dt variable as IEnumerable
here is my attepmt to do
mainDT.AsEnumerable.GroupBy(Function(r ) r(“Account Number”)).Select(Function (g) g.OrderByDescending(Function(r ) Convert.toDouble(r(“Available Balance”).ToString)))

dont get any error but i want to return as a dadatable. but it’s not allowed to do

1 Like

Hi @phyomt
Check this

DataTable Group By Sum and Count using Linq

Instead of sum use max function
thanks
a
Ashwin.S

1 Like

Hi @phyomt,

It can help to solve your probelm.

Regards
Balamurugan.S

2 Likes

Thanks for that. Unfortunately we are not allowed to install third party activity unless it’s from uipath directly :frowning:

1 Like

i tried using it to see if it works for me or not but i m getting syntax error from it
Sequence: Syntax error in aggregate argument: Expecting a single column argument with possible ‘Child’ qualifier.

1 Like

@phyomt

I hope I’m going to confuse you, you just need to create a pivot table and use the account number in the rows field and the available balance in the values field and then change the aggregation rule to max which will give you the required value. As simple as that

1 Like

That’s what i did . I have test datatable with 5 columns. All string type and tried to use it. Didn’t work

1 Like

Not sure how you did that, even it is a string type, it will aggregate the values when we change it to aggregation rows. Can you post the screenshot of whatever you tried?

1 Like

Hi here’s what i did.

and here is the error

and this is error message
Group By Aggregation: Syntax error in aggregate argument: Expecting a single column argument with possible ‘Child’ qualifier.

2 Likes

Oh, I hope you didn’t understand what I mean. I’m just asking you to do the pivot in excel itself so that you can copy that data table and use it further

1 Like

arh that’s not what i want to do. i need to do that before storing to the excel. Thanks for quick reply

1 Like

So, you can pivot it in another excel and move it to the required excel right?

I’m just trying to build an activity to pivot using the excel columns. that will use in this scenario. Will update you once that is done

1 Like

Hi @phyomt ,

Here is the output for your excel file.

Sample : Phyomt.zip (26.1 KB)

Regards
Balamurugan.S

1 Like

hi @balupad14 , thanks for that, the thing is i m trying to group and get the value directly from the DT (i can’t store it to excel file yet as the value are constantly updated in DT). Not from Excel. Somehow the activities are not working that way if I am trying to get the value directly from DT.

2 Likes

Hi @phyomt

To show the output I have written the datatable to Excel. But the result is in the dtOutput datatable variable…

Thank you
Balamurugan.S

1 Like

Thanks, So i need to assign all columns that will be use inside aggregation ? can’t i directly input and use the column name inside rather than using assign activity to make all columns

1 Like

No . You can able use only one column in that activity. I have another activity called “Native Group Aggregation”. But the things is, it doesn’t do the type conversion for the columns . We have to do manually. So to convert the datatable column dataType , we have to use an activity from @Jyotsna from UiPath Labs . I have illustrated an example for you.

Input :
image

OutPut :

Xmal format :

Sample : Phyomt.zip (27.3 KB)

Regards
Balamurugan.S

2 Likes

Hi, thanks for all the reply and solutions. I still would like to try with Linq statement as i m trying to learn using linq in uipath. Any advice appreciate.