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

[whitestar (Phyo)]

Hi everyone,

I am trying the below scenario.

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

[AshwinS2 (Ashwin S)]

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

[whitestar (Phyo)]

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 ?

[AshwinS2 (Ashwin S)]

create a dt variable with assign activity

Thanks
ashwin S

[whitestar (Phyo)]

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

[AshwinS2 (Ashwin S)]

Hi @whitestar
Check this

DataTable Group By Sum and Count using Linq

Instead of sum use max function
thanks
a
Ashwin.S

[balupad14 (Balamurugan)]

Hi @whitestar,

It can help to solve your probelm.

BalaReva.DataTable.Activities

Hi Friends. I have included some activities in the package of BalaReva.Datatable.Activities You can see in the below namespace [image] The key advantages are below 1.ConvertInputColumn - When you read the data from excel , it is always string format. Using this property can able change the data type and do the aggregation . 2.NullValue - If you have empty of null value in the data. can able to assign the default value for it. 3.Select- It is place to keep the condition(Where) Sample Fil…

Regards
Balamurugan.S

[whitestar (Phyo)]

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

[whitestar (Phyo)]

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.

[HareeshMR (Hareesh Madasi)]

@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

[whitestar (Phyo)]

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

[HareeshMR (Hareesh Madasi)]

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?

[whitestar (Phyo)]

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.

[HareeshMR (Hareesh Madasi)]

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

[whitestar (Phyo)]

@HareeshMR

ivot in excel itself so that you can copy that data table and use it further

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

[HareeshMR (Hareesh Madasi)]

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

[balupad14 (Balamurugan)]

Hi @whitestar ,

Here is the output for your excel file.

Sample : Phyomt.zip (26.1 KB)

Regards
Balamurugan.S

[whitestar (Phyo)]

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.

[balupad14 (Balamurugan)]

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

[whitestar (Phyo)]

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