Filter datatable using different unique values of a column and Sum the Amount column of each filtered datable

Hello Community!!

Please I need your help.

I have a data table with many columns but i want to filter the data table using the ID column and sum the Amount column of the filtered data table. So the condition is I want to filter using “start with”. So any ID that start with 1234… we filtered it and sum the total amount. Then we filter the ID column again with 2345… and sum the total amount. So in total I have 7 IDs that I want to filter and sum the corresponding Amount column


Follow the steps

  1. Use a for each activity with in argument as {"1234","2345",and so on} and change type argument to string
  2. Now inside the for eqch activity use a filter datatable activity with dt as input and filtereddt as output and column as "ID" and value as currentitem
  3. To get sum using the below formula filtereddt.AsEnumerable.Sum(function(x) CDBLx("Amount").ToString)) in assign activity

Hope this helps

If you want all the sums in a datatable with each geoup into a datatable you can use a linq query as well…for that first use a build datatable activity and add two columns of type string one for first 4 digits of ID and one for sum and name the datatable as outputdt

And then use assigna ctivity as below

Outputdt = (From d In dt.AsEnumerable Group d By k=d("ID").toString.Trim.SubString(0,4) Into grp = Group Let s1 = grp.Sum(function(x) CDBL(x("Amount").ToString)).ToString Let ra = New Object(){k,s1)} Select r = outputdt.Rows.Add(ra)).CopyToDataTable

Here dt is the input datatable


Thank you, @Anil_G The Linq query solved my problem. Thanks

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.