Sum Column in DataTable with a Where Clause

Help Studio
, ,
1

Hello, I would appreciate help on issue I’m facing.
Objective: I want to perform the following tasks on Excel worksheet.

  1. Filter ‘Unit Cost’ column value = 0 and ‘Vendor’ column value = (‘A’ or ‘B’ or ‘C’ or ‘D’)
  2. Based on output of filtered columns, Sum ‘Total’ column values where ‘Category’ = (“1234” OR “5678”) - Assign result to variable called ‘Group1’

Summary of steps taken so far are;

  1. In Excel Application Scope activity, used Read Range activity to read data from file and output as variable ‘InputDT’ DataTable datatype
  2. Used Filter Data Table activity to apply filters based on my filter criteria and output as variable to ‘OutputDT’ DataTable datatype.
  3. Use Assign activity to perform sum based on where conditions and output result to variable with data type of String.
    LINQ query used in Assign activity is CStr(OutputDT.AsEnumerable().Where(Function(row) row.Field(Of String)(“Category”) = “820” Or row.Field(Of String)(“Category”) = “420”).Sum(Function(row) CDbl(row.Field(Of String)(“Total”))))

I get error message
image

I would appreciate responses to fixing this issue. Thanks

2

Try Convert.ToDouble(row(“Total”).ToString) inside Sum LINQ method

3

Hey
Try this below query

(OutputDT.AsEnumerable().Where(Function(row) row.Field(Of String)(“Category”) = “820” Or row.Field(Of String)(“Category”) = “420”).Sum(Function(row) CDbl(row.Field(Of String)(“Total”)))).ToString()