plekocaj
(Plekocaj)
October 21, 2021, 1:55pm
1
I need help invoking a code to add inputs together in an excel sheet. My workflow starts as executing a SOQL activity to pull data from salesforce, i take this data and write it to an excel sheet using write range. The output is SalesforceReportDT. In the excel it shows two rows, the ID and Amount. There are multiple ID entrys for some IDs. Now i need to invoke code so i can get the excel sheet to combine all the same ID’s and add the ammount together to just have one row for the ID. Th excel sheet looks like this.
100011 1000
100011 500
100011 300
100012 2000
100012 500
100012 1000
100012 2000
100013 2000
100013 1000
100013 1000
100014 0
100014 500
100014 1000
100016 500
100040 1000
100040 1000
Hi
It’s more like groupby and sum the column value
Have a view on this thread with similar scenario
How do I group by one column in the datatable and take the sum of another column based on this grouping. I have attached a sample file for better understanding.group and sum.xlsx (9.7 KB)
Pls revert back if that’s not the one you are looking for
Cheers @plekocaj
postwick
(Paul)
October 21, 2021, 2:40pm
4
You should manipulate the data in SalesforceReportDT before writing it to Excel, not write it to Excel then try to manipulate it in Excel.
ppr
(Peter Preuss)
October 21, 2021, 2:45pm
5
as you have already the data you can do it with a groupby on ids and summing up the amunts as described there:
we can do it with the help of a group by
prepare the report table with build datatable (configure two or needed coumns)
OR
Assign activity
LHS: dtReport | datatable
RHS: YourOriginDataTable.Clon
then use again an assign Activity
LHS: dtReport
RHS:
(From d in YourOriginDataTable.asEnumerable
Group d by k=d(0).toString.Trim into grp=Group
Let s = grp.Sum(Function (x) CInt(x(1).toString.Trim))
Let ra = new Object(){k,s}
Select r=dtReport.Rows.Add(ra)).CopyToDataTable
Also have a look here…
Also check if it can be done directly on the datatabase with corresponding SQL, an option which one is also to prefer
ppr
(Peter Preuss)
October 21, 2021, 2:54pm
7
tested with a mini set approach from above:
Flow
I/O
(From d In dtData.asEnumerable
Group d By k=d(0).toString.Trim Into grp=Group
Let s = grp.Sum(Function (x) CInt(x(1).toString.Trim))
Let ra = New Object(){k,s}
Select r=dtResult.Rows.Add(ra)).CopyToDataTable
Find starter help here:
GroupBy_1Col_Sum1Col_Demo.xaml (7.8 KB)
ppr
(Peter Preuss)
October 21, 2021, 3:02pm
9
refer to updated post above
ppr
(Peter Preuss)
October 21, 2021, 6:19pm
11
Have check on screenshot:
Your case was interpetated like: group data by ID and sumup the Amounts:
100011 | (1000+100) = 1100
100012 | (2000+200) = 2200
We did understand your case in the right was?
Your processed datatable has the same structure as you mentioned above?
In case of it is not working as expected, just share some details from your flow and data