Merge datatable rows basis one column and sum amount

Help Studio
1

I have a data table with 4 columns as follows

TransactionID, Date, Cost, Amount
749348828, 12-03-2022, 199, 500
474972940, 23-03-2022, 299, 299
749348828, 13-03-2022, 399, 500

How can I combine rows with same TransactionID and sum Cost and Amount for those two rows and add to 1st row
Result datatable should be

TransactionID, Date, Cost, Amount
749348828, 12-03-2022, 498, 1000
474972940, 23-03-2022, 299, 299

Pls help me with this ASAP
Thanks in advance

2

Hi @Vamshi_Krishna_Aluvala ,

Could you give this a try?

Dt.AsEnumerable().
GroupBy(Function(g) g("TransactionID").ToString).
	Select(Function(s) dt.Clone.LoadDataRow({
	s.Key,
	s.First().Item("Date").ToString,
	s.Sum(Function(su) CInt(su("Cost").ToString.Trim)),
	s.Sum(Function(su) CInt(su("Amount").ToString.Trim))},False)).CopyToDataTable()

SelectFirstItem.xaml (7.4 KB)

Kind Regards,
Ashwin A.K

1 Like
3

Hi @Vamshi_Krishna_Aluvala

Try this expression

(From p In DtMaster.AsEnumerable() Group By x= New With { Key.a =p.Item("TransactionID")} Into Grp = Group Select DT.Clone.LoadDataRow (New Object() {grp(0)(0),grp.Sum(Function ( r ) if(String.IsNullOrEmpty(r(“Amount”).ToString) or String.IsNullOrWhiteSpace(r(“Amount”).ToString),0,CDbl(r(“Amount”).ToString)))},False)).CopyToDataTable

Regards
Gokul

4

Perfect.
Thank you so much

5

Glad we could help!

I’d appreciate it if you marked the answer as solution so that others facing similar issues may benefit from it also so that we may close this thread.

Have a nice day!

Kind Regards,
Ashwin A.K

6

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