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)
@jamnanin, Refer this,
I did read this but i am unable to execute it. could you provide a work flow? It would be very helpful.
good that you have done this
Try from your end and upload the xaml here. Paste the error you derive as it would be helpful for the people who looks and get this issue.
I mean I am unable to follow it.
I also tried the .compute method but i get this error.
consolidateTableCompute has thrown an exception
Message: Invalid usage of aggregate function Sum() and Type: Object.
Exception Type: DataException
System.Data.DataException: Invalid usage of aggregate function Sum() and Type: Object.
at System.Data.Common.ObjectStorage.Aggregate(Int32 records, AggregateType kind)
at System.Data.DataColumn.GetAggregateValue(Int32 records, AggregateType kind)
at System.Data.AggregateNode.Eval(Int32 records)
at System.Data.DataExpression.Evaluate(DataRow rows, DataRowVersion version)
at System.Data.DataTable.Compute(String expression, String filter)
at lambda_method(Closure , ActivityContext )
1.Execute(CodeActivityContext context) at System.Activities.CodeActivity1.InternalExecuteInResolutionContext(CodeActivityContext context)
at System.Activities.Runtime.ActivityExecutor.ExecuteInResolutionContext[T](ActivityInstance parentInstance, Activity
1 expressionActivity) at System.Activities.InArgument1.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance activityInstance, ActivityExecutor executor)
at System.Activities.RuntimeArgument.TryPopulateValue(LocationEnvironment targetEnvironment, ActivityInstance targetActivityInstance, ActivityExecutor executor, Object argumentValueOverride, Location resultLocation, Boolean skipFastPath)
at System.Activities.ActivityInstance.InternalTryPopulateArgumentValueOrScheduleExpression(RuntimeArgument argument, Int32 nextArgumentIndex, ActivityExecutor executor, IDictionary
2 argumentValueOverrides, Location resultLocation, Boolean isDynamicUpdate) at System.Activities.ActivityInstance.ResolveArguments(ActivityExecutor executor, IDictionary2 argumentValueOverrides, Location resultLocation, Int32 startIndex)
at System.Activities.Runtime.ActivityExecutor.ExecuteActivityWorkItem.ExecuteBody(ActivityExecutor executor, BookmarkManager bookmarkManager, Location resultLocation)
Can you check this. It helps you.
file : group by one column in the datatable and take the sum.zip (3.1 KB)
I will suggest you one more way try like this.
Let us take your excel stored in a datatable dta
First group the datatable based on ID by using below Query and assign it to dt1
dt1=(From p In dta.Select()
Group p by ID=p.Item(“ID”).ToString Into Group
Now take only two columns by using below query
Now Add one datacolumn to dt1 by using add datacolumn activity and give name Amount to that column
Then create one list of strings ListA
Then use this query
ListA=(From p In dta.Select()
Group p By ID=p.Item(“ID”).ToString Into GroupA=Group
Select Convert.ToString(GroupA.Sum(Function(x) Convert.ToDouble(x.Item(“Amount”).ToString)))).ToList()
Next Run one Foreach loop for dt1
Inside for each use one assign activity
Now your dt1 will contain the required output and you can write to excel by using WriteRange.
Try this with less code.
i cann’t found the “Amount” in the LisA assignation