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

Hi everyone,

I am trying the below scenario.
test

There is a total of 5 columns I m just showing what I need. All the input will be on String input.
So I want to group duplicate ID and use the highest available balance to do the calculation in another step.

Say in my test data for account number 11111 the highest number is 2331
So i will do the calculation using that number 2331
Let’s say need to minus 1000 from 2331
the final available balance become 1331.
Then i will use what’s left of that balance to update the DT and output all the data to excel file

Now the problem become in second run.
If i see this account number again in another transaction I will need to compare the available balance again with previous highest number from excel data and use the highest balance to do the calculation.

Let’s say in excel file it’s 1331 which is highest amount from previous transaction but in second time the avaiable balance is 2000 then i will need to use this to do the calculation.
then do the calculation and update the amount again in excel file.

Hope I make it clearly.
Thanks

1 Like

Hi @whitestar

try this
(From row In DT.AsEnumerable() Group row By INV =New With { Key .INVNum = row.Item(“ Groupbycolumnname ”)} Into INVGroup = Group Select New With {.INVNumber = INV.INVNum, .Sum=INVGroup.Max(Function® Double.Parse(r.Item(“ SummationColumnName ”).ToString()))}).ToList

Thanks
ashwin S

1 Like

hi thanks for the reply,

it’s using the LinQ statement ? do i need to create new variable or can use in for each row ?

1 Like

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 @whitestar
Check this

DataTable Group By Sum and Count using Linq

Instead of sum use max function
thanks
a
Ashwin.S

1 Like

Hi @whitestar,

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

@whitestar

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 @whitestar ,

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 @whitestar

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