How to get sum of column from datable?

Help Studio
1

Need to claculate the sum of column of specific data columns from data table also can anyone explain if any of column cell contain null value or no value how we can consider column value as zero and calculate the sum of column

2

@omkard222 It would be Great if you would give us an Input and Tell What Column to be Summed up and What should be the Expected Output, Sample Data is Appreciated :sweat_smile:

3

@omkard222 You can do Something like this :
DT.AsEnumerable.Sum(Function (x) If(Double.TryParse(x.item(“Column2”).ToString, Nothing), Double.Parse(x.Item(“Column2”).ToString), 0)) to get the Sum of One Column

4
Orig Amount Local Orig Amount USD
94509.33 122087.06
91538.42 102075.32
299128.29 81442.76
495792.75 74002.03
98540.77 72389.92
58512.12 65247.39
204492.18 57988.05
215393.22 57434.39
201858.56 54959.42
48621.03 54217.75
408281.03 52091.35
190729.39 51929.32
5

@supermanPunch these are columns which i want to sum separately. can you just provide vb expression by considering these column

6

@omkard222 I guess you can use these two for Calculating the Sum of those Two Columns :

DT.AsEnumerable.Sum(Function (x) If(Double.TryParse(x.item(“Orig Amount Local”).ToString, Nothing), Double.Parse(x.Item(“Orig Amount Local”).ToString), 0))

DT.AsEnumerable.Sum(Function (x) If(Double.TryParse(x.item(“Orig Amount USD”).ToString, Nothing), Double.Parse(x.Item(“Orig Amount USD”).ToString), 0))

Why vb Expression? How do you want the Output to be?

7

@supermanPunch Thank you so much, the methods you are using are LINQ method rite?

8

@omkard222 Yes :smile:

9

@supermanPunch Do you ever using linq for joining the data table with if conditions?

10

@omkard222 Yes, but the if Conditions that i have used are always based on Equals in Linq

11

@supermanPunch let me elaborate about my scenario…

EmployeeDT

Orig Employee Id Orig Cost center
110340 555300
103767 139410
107208 135570
109276 134345
104789 153603
104001 135402
105790 135555
109380 133575
111877 185570

Details DT

Orig Cost center Company Code Valid To Date
555300 4100 31-12-9999
139410 5700 31-12-9999
135570 4100 31-12-9999
134345 5700 31-12-2010
153603 4100 01-01-2011
135402 5700 02-01-2011

i have these two columns in EmployeeDT, i want to extract “Company Code” from another DetailsDT datatable where Cost Center equals to the Cost center from Details DT and “Valid to Date” should be greater than system date.

Let me know how i can i achieve this using LINQ

12

@omkard222 Have you tried using Join Datatables Activity?

13

@omkard222 I guess the best way would be Join the Datatables based on Cost Center Values either by using Linq or Join Activity, Then Since Date is not comapred with other Datatable, you can use it Filter Datatable and Filter it, or it can be done by using Linq as well

Or if you want a complex approach then to do both operation in a Single Linq query is also possible :sweat_smile:

1 Like
14

@supermanPunch i dont have that much knowledge of LINQ so can you just provide me sytax for join columns with if condition?

15

@omkard222 In the Workflow that I have provided, There’s a Linq Query , I Hope you can get the Syntax From it, Check the Input Files, And Check the Output, I hope that’s the Output you needed :sweat_smile:
JoinDT.zip (13.1 KB)