How to Group by Columns and Sum

[AaronMark]

Hi
I’m stuck in this passage.
I have to compare two tables, collected on two web pages.
In a table, the data is compacted.
In the other they are open by line.

From site 1:

Purchase_Order	Line #	Submitted Amt	TimeSheet
0410008151	1	3500	2733
0410026193	1	274530,36	10905

From Site 2:

Purchase_Order	Line #	Submitted Amt	TimeSheet
0410008151	1	3500	2733
0410026193	1	6051,12	10905
0410026193	1	64175,73	10905
0410026193	1	15128,96	10905
0410026193	1	7324,88	10905
0410026193	1	72469,2	10905
0410026193	1	75766,98	10905
0410026193	1	33613,49	10905

I need to group by Purchase_Order and PO_Line,
and add the related ammounts.

Then I have to compare the two tables.

TableCompare.xlsx (9,7 KB)

I have no experience.
Can anyone help me in this?

[SenzoD (Senzo Dlomo)]

@AaronMark, Hi

Please check this, it might help you solve this issue:

The pack has an activity that allows you to group by aggregation, just specify the column which you want to group by and the column which you want to aggregate.

BalaReva.DataTable.Activities

It has the custom activities for the easy functionality of datatable under the namespace of DataTableExtensions. 1.Add Data Row Range. 2.Remove Data Row Select. 3.Group By Aggregation (Sum,Count,Avg, Max, Min). Package: BalaReva.DataTable.Activities Author: @balupad14

[AaronMark]

Hi Senzo,
thank you for your suggestion.
I was reading, but I am unfamiliar with UIPath.
Can you give me a hand? I’m stuck here and I can’t keep going.

[AaronMark]

It seems not to work.
Maybe a Submitted Amt format to edit?
the Submitted Amt column is created as a Decimal and then converted as it-IT

The job requires me to compact data into the “Snow” sheet,
(group by “Concat” and sum of “Submitted Amp”)
and verify that it matches the total on the Coupa sheet.

GroupByAggregation.xaml (8,5 KB) 10905-Timesheet_Found.xlsx (13,1 KB)

Can Help me ?

[Gabriel_Lim]

Should be naming issue only,after switch to SubmittedAmt,it works fine.

[ganesh4 (Ganesh)]

Adding a subtotal row based upon like names

I have tried the balareva group by aggregation and it does not work. I have tried joins and it does not work. Any help anyone can give would be wonderful. In the attached data example.xlsx (9.2 KB) I am trying to sum by Employee and insert a subtotal below that group of line items for each employee aggregating Allocated Expense Amount. I think I have tried just about everything and am to the point where I am telling the business this is not possible. Does anyone have any ideas?

FYR

[Chris_Bolin]

We received the same error with BalaReva. Please see the response from ganesh4

[AaronMark]

Hello everybody.
Sorry for the delay in my reply.
I found the notifications to this very old request.

I solved it, but in a different way.

Not being able to use Excel Application Scope, not being able to use unofficial packages, I imported the table into the database, then, with some Queries I worked the output as needed.

[AaronMark]

Hi, Lin,
as I said, I solved it by importing the data to a database, and then aggregating it with a SQL query.

After collecting the data and creating the table,
I import everything on DB.

In my flow, I first clean up the table from the previous processing.

Then, then, with a query, SUM and AGGREGATE.

My Query:

“SELECT concat(TimeSheet,PurchaseOrder,PO_Line,sum(Submitted)) as Concat
FROM rpd_bot1_snowcoupa_outsnow
WHERE
TimeSheet = '”+TimeSheetPA+“’ AND PurchaseOrder = '”+PurchaseOrderPA+“’
GROUP BY PO_Line”

In your example, if I understand correctly:
SUM of
and double grouf of and

[amdrafa (Rafael Amaro)]

Man, I have expended hours trying to solve it until I see your comment! thanks so much!