Linq Query to Sum the multiple columns in the datatable

[aravinthan.k]

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

[Baskar_Gurumoorthy]

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()

[mrohan.senapaty (Rohan)]

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()

[aravinthan.k]

@mrohan.senapaty

(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()

Hi Getting Compiler issue

[aravinthan.k]

Hi Getting Compiler issue

[Baskar_Gurumoorthy]

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

[aravinthan.k]

@Baskar_Gurumoorthy

(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()

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

[Baskar_Gurumoorthy]

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

[Ajay_Mishra (Ajay Mahendra Mishra)]

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:

Workflow Screenshot:

Argument of Invoke Code:

Input & Output File:

Input.xlsx (9.0 KB)

Attaching .xaml for your reference:

SumofColumns_Sequence.xaml (7.9 KB)

Regards,
Ajay Mishra