Linq Query to Sum the multiple columns in the datatable

Help Studio
,
1
  1. Filter Particular Site and Sum multiple Columns
Input
Site Total 1-Apr 2-Apr 3-Apr 4-Apr 5-Apr 6-Apr 7-Apr 8-Apr
Chennai 4 0 2 4 66 44 50
Pondy 4 22 3.2 4 10 5 0 41

Filtered Particular Site Chennai and to sum all columns

Output
Site Total 1-Apr 2-Apr 3-Apr 4-Apr 5-Apr 6-Apr 7-Apr 8-Apr
Chennai 170 4 0 2 4 66 44 50
2

Hi @aravinthan.k
after filtering the site you can use the below code to sum all the column values
Assign: rowSumList = (From row In dtData.AsEnumerable()
Let sum = Enumerable.Range(0, dtData.Columns.Count).Sum(Function(i) If(IsNumeric(row(i)), CDbl(row(i)), 0))
Select sum).ToList()

3

outputDataTable = (From row In inputDataTable.AsEnumerable() Group row By site = row.Field(Of String)(“Site”) Into Group Let total = Group.Sum(Function(r) If(IsNumeric(r.Field(Of String)(“Total”)), Double.Parse(r.Field(Of String)(“Total”)), 0)) Let dateSums = Enumerable.Range(1, 8).Select(Function(i) Group.Sum(Function(r) If(IsNumeric(r.Field(Of String)(i)), Double.Parse(r.Field(Of String)(i)), 0))) Select outputDataTable.Rows.Add({site, total}.Concat(dateSums).ToArray())).CopyToDataTable()

4

Hi Getting Compiler issue

5

Hi Getting Compiler issue

6

please share the screenshot of the error, please replace the indata with your data table name

7

I have kept datatable name as dtData only and assigned rowSumList as List and find below error

Issue
Issue934×321 91.6 KB

8

please create variable as 'sum" assign sum target type as System.linq.enumerable.range

9

Hey @aravinthan.k

Use below mentioned query in Invoke Code Activity:

io_dt_Input = io_dt_Input.AsEnumerable.Where(Function(a) a("Site").Tostring.Tolower.Trim.Equals("chennai")).CopyToDataTable

io_dt_Input.AsEnumerable.ToList.ForEach(Sub(x)
x("Total") = x.ItemArray.Skip(2).AsEnumerable().Where(Function(y) y.ToString.IsNumeric).ToArray.Sum(Function(z) CInt(z))
End Sub)

Note: If you want to add more filter on Site Column then edit above query’s first line,
Like below mentioned Example:

io_dt_Input = io_dt_Input.AsEnumerable.Where(Function(a) a("Site").Tostring.Tolower.Trim.Equals("chennai") And a("Site").Tostring.Tolower.Trim.Equals("mumbai")).CopyToDataTable

Input & Output Screenshot:
image

Workflow Screenshot:

image
image974×595 21.7 KB

Argument of Invoke Code:

image
image947×361 16 KB

Input & Output File:

Input.xlsx (9.0 KB)

Attaching .xaml for your reference:

SumofColumns_Sequence.xaml (7.9 KB)

Regards,
Ajay Mishra