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
@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
@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
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 |
@supermanPunch these are columns which i want to sum separately. can you just provide vb expression by considering these column
@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?
@supermanPunch Thank you so much, the methods you are using are LINQ method rite?
@omkard222 Yes
@supermanPunch Do you ever using linq for joining the data table with if conditions?
@omkard222 Yes, but the if Conditions that i have used are always based on Equals in Linq
@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
@omkard222 Have you tried using Join Datatables Activity?
@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
@supermanPunch i dont have that much knowledge of LINQ so can you just provide me sytax for join columns with if condition?
@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
JoinDT.zip (13.1 KB)