Hi I want to iterate through a column values to check for same debit acc and if the debit acc is same I want to add the debit amount for all those rows
ex:
debit acc|debit amount
123 |500
456 |200
123 |300
123 |400
890 |200
I want the bot to iterate through debit acc column look for common values and sum those up.
Best option would be to write this data table to an excel and create pivot table in excel using Studio. Read the pivot table output and will give you the expected result
Follow the same steps included in the below workflow. Dattable2 will have the expected output. You can delete the Total row if not required
we are using excel as an intermediate option to get the required results. This will give the result in same time irrespective of the row count as we are not looping anywhere
Use a For Each Row activity to iterate through the DataTable. Create a dictionary (debitSums). In the loop, check if the Debit Acc exists in the dictionary. If it does, add the Debit Amount to the sum, otherwise, add a new entry. After the loop, the dictionary will contain the summed amounts for each Debit Acc.
Happy Automation!
we can achieve it by using a group data approach
[HowTo] Overview on different options for grouping data and processing the groups - News / Tutorials - UiPath Community Forum
Hey @manisha.k.chand
you can try this way:
BlankProcess8.zip (36.9 KB)
groupedSums = (From row In dtInput.AsEnumerable()
Group row By acc = row("debit acc").ToString.Trim Into grp = Group
Select acc, total = grp.Sum(Function(r) CDbl(r("debit amount")))
).ToDictionary(Function(g) g.acc, Function(g) g.total)
e.g. with a Linq approach
Flow:
LINQ:
(From d In dtData.AsEnumerable()
Group d By k=d("debit acc").toString.Trim Into grp=Group
Let sum = grp.Sum(Function (c) CDbl(c("debit amount")))
Let ra = New Object(){k,sum}
Select r = dtResult.Rows.Add(ra)).CopyToDatatable
Check:
LINQ_Query.zip (142.4 KB)
You can use LINQ query to get the output using just one ‘assign’ activity.
dt_Output = (From row In dt_Input.AsEnumerable() Group row By acc = row(“debit_acc”).ToString() Into grp = Group Select dt_Input.Rows.Add(acc, grp.Sum(Function(x) Convert.ToDouble(x(“debit_amount”))))).CopyToDataTable()
Please find the attached.
Just as an experiment to test ‘Autopilot’ , I typed your question to ‘Autopilot’ as it is and generated the sequence. The approach it is taking is using Dictionary type object. A bit more complicated way of doing it. However, not close to producing any output by itself.
Hello please refer the below attached xaml for your solution:
Input File which will contain the data provided by you.
Main.xaml (16.0 KB)
