How to exclude negative numbers from sum?

Build
#1

Hi. I’ve got the following in an assign activity:

dt.AsEnumerable.Where(Function(x) x(“Column name”).ToString.Trim.Equals(variable)).Sum(Function(x) Convert.ToDouble(x(“Column name 2”).ToString.Trim) ).ToString

This loops through the DT, and sums duplicates of Column name (a number) and sums all sums in Column name 2 (also numbers). However, Column name 2 sometimes includes negative numbers, and I’d like to exclude those. Any input on how to modify my code to do this?

#2

Hi it’s very messy linq-expression. Why you always cast to String? First cast to numerical type, than make operations. But you can modify your code like that if I understand your right

dt.AsEnumerable.Where(Function(x) x(“Column name”).ToString.Trim.Equals(variable) AndAlso ).Select(Function(x) Convert.ToDouble(x(“Column name 2”).ToString.Trim) ).Where(Function(x) x>0).Sum.ToString

#3

Hi @Uemoe and thanks. When I use that code I get a compiler error; “Expression expected”

#4

Try this one:

dt.AsEnumerable.Where(Function(x) x(“Column name”).ToString.Trim.Equals(variable) ).Select(Function(x) Convert.ToDouble(x(“Column name 2”).ToString.Trim) ).Where(Function(x) x>0).Sum.ToString

Be aware of quotes forum spoilt them

#5

You can use the code formatting to make sure that those quotes are correct:
dt.AsEnumerable.Where(Function(x) x("Column name").ToString.Trim.Equals(variable) ).Select(Function(x) Convert.ToDouble(x("Column name 2").ToString.Trim) ).Where(Function(x) x>0).Sum.ToString

You use it by encapsulating things with two backticks ``

#6

@rlohne

You can try like this also

StrSum = (From p In dt.Select 
Where p(“Column name”).ToString.Trim.Equals(variable) and Convert.ToDouble(p(“Column name 2”).ToString.Trim)>0 
Select Convert.ToString(p(“Column name 2”))).Sum(Function(x) Convert.ToDouble(x)).ToString

Regards,
Mahesh

1 Like
#7

Thanks guys! Worked like a charm.

#8