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.

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

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

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.

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

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?

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

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

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.

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