Calculating SUM of "Debit" Column using Group by "TransactionNo" Column

[nidheessingh]

Hello,

I have an input datatable and produce an output datatable using LINQ.
Anyone, please help.

Regards,
Nidhees

[nidheessingh]

Hello @Palaniyappan,

“TransactionNo” column has duplicate value and accordingly, i have to SUM the “Debits” column value for each duplicate transaction no.

Regards,
Nidhees

[Palaniyappan (Forum Leader)]

Yay sorry… I didn’t see that clearly,
Let me check once buddy
Cheers @nidheessingh

[Palaniyappan (Forum Leader)]

Hope would help you @nidheessingh

Outdatatable = (from table in dt.AsEnumerable()
group table by table.Field(“TransactionNo”)
into groupedTable
select new
{TransactionNo = groupedTable.Key,
Debits = groupedTable.Sum(x => x.Field(“Debits”))}).ToArray.CopyToDatatable()

Where Outdatatable is a variable of type datatable

Kindly try this and let know whether this works or not buddy
Cheers @nidheessingh

[AshwinS2 (Ashwin S)]

Hi @nidheessingh

Try this as well

ExcelData.AsEnumerable.Sum(Function(x) Convert.ToDouble(x(“Debits”).ToString.Trim) ).ToString

Thanks
Ashwin S

[nidheessingh]

Hello @Palaniyappan,

I am getting compiler error on first query as below…

Regards,
Nidhees

[Palaniyappan (Forum Leader)]

Aah sorry one second
Cheers @nidheessingh

[Palaniyappan (Forum Leader)]

Yes remove one of the curve bracket here lik this
…x.Field(“Debits”)})

Cheers @nidheessingh

[nidheessingh]

Hello @Palaniyappan,

Still not working…

Regards,
Nidhees

[nidheessingh]

Hello @ClaytonM,

Could you please help regarding this query?

Regards,
Nidhees

[Palaniyappan (Forum Leader)]

May i what was the error that occurred @nidheessingh

[balupad14 (Balamurugan)]

hello @nidheessingh,

Can you try this ?

BalaReva.DataTable.Activities

Hi Friends. I have included some activities in the package of BalaReva.Datatable.Activities You can see in the below namespace [image] The key advantages are below 1.ConvertInputColumn - When you read the data from excel , it is always string format. Using this property can able change the data type and do the aggregation . 2.NullValue - If you have empty of null value in the data. can able to assign the default value for it. 3.Select- It is place to keep the condition(Where) Sample Fil…

Regards
Balamurugan.S

[ClaytonM]

In your previous image, you have “=>” when declaring ‘x’, which is a C# syntax. You need to use .net syntax which would be .Sum(Function(x)
However, I think you have other issues with your LINQ code.

You can check out this post here about using GroupBy for Sum:

Linq Group By and Sum

Hello, I’m not sure this is the best way, however I got this to work by manipulating the data to a delimitted format using the below steps. Store GroupBy expression to a variable of type IEnumerable< Object > [image] The Output of this returns: [image] Concatenate the header to the items in the list using .Split and .Select You can split by comma, then use Select, and select the item split by “=” [image] Without the headers, we have this: [image] So our new output looks like thi…

Regards.

[nidheessingh]

Hello @balupad14,

I have used the Aggregate function “SUM” on a “Debits” column along with “Group by” on “TransactionNo” but while execution, I am getting error on row no-101755.

I checked the data on these specific cells(i.e. “Debits” and “TransactionNo” columns) and everything is OK.

Input Sheet:

Could you please help me where is the issue?

Regards,
Nidhees

[balupad14 (Balamurugan)]

Hi ,

Can you attach the file to test at my side.

Regards
Balamurugan.S

[nidheessingh]

Hello @balupad14,

Please find the updated sheet with few data.
In this sheet, I am getting an error at row# 18.

Regards,
Nidhees

BS_Input.xlsx (10.0 KB)

[balupad14 (Balamurugan)]

Hi @nidheessingh,
I will let you . after it verified.

Thank you
Balamurugan.S

[balupad14 (Balamurugan)]

Hi @nidheessingh ,

The problem is here

The cells should not be blank. It must have the value. I have set it as zero.
It will give result like below

Sample : nidheessingh.zip (9.5 KB)

Regards
Balamurugan.S