LINQ Query for Group BY, Sum and Condition After Sum

Help Studio
, ,
1

Input
The image shows a data table with columns for id, Amount, Name, and Product, containing records with various entries. (Captioned by AI)
Output
image

I need help with LINQ where I have to groupby ID and if SUm of AMOUNT is greater than ZERO then retrurn all the rows as shown above

Please help
@ppr @Yoichi

2

Hi @Divyanshu_Divyanshu

Try this

outputDT = inputDT.AsEnumerable().GroupBy(Function(row) row("id").ToString()) _
           .Where(Function(group) group.Sum(Function(r) Convert.ToDecimal(r("Amount"))) > 0) _
           .SelectMany(Function(group) group).CopyToDataTable()

Regards,

3

Give a try at
Assign activity
dtResult =

(From d in dtData.AsEnumerable
Group d by k=d("id").toString.Trim into grp=Group
Let sc = grp.Sum(Function (x) CInt(x("Amount").toString.Trim))
Where sc > 0
From g in grp
Select r=g).CopyToDataTable

Handling empty results:

4

Hi @Divyanshu_Divyanshu

=> Read Range Workbook
Output → InputDt
image

=> Use below syntax in Assign:

resultDt = (From row In InputDt.AsEnumerable()
                Group row By key = row.Field(Of String)("id") Into Group
                Let TotalAmount = Group.Sum(Function(r) r.Field(Of Double)("Amount"))
                Where TotalAmount > 0
                From r In Group
                Select r).CopyToDataTable()

=> Write Range Workbook resultDt back to excel in new sheet.
image

FLOW:

The image depicts a workflow sequence where data is read from "Input.xlsx" (Sheet1), assigned to a variable , and then written into "Input.xlsx" (Sheet2). (Captioned by AI)
The image depicts a workflow sequence where data is read from "Input.xlsx" (Sheet1), assigned to a variable , and then written into "Input.xlsx" (Sheet2). (Captioned by AI)891×576 27.5 KB

Regards

5

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.