Find duplicate values in data table and do operation as per condition in the same table

Help Studio
1

I have one Table. It has a list of Account numbers, transaction ID numbers in the form of “DC12345” and their corresponding Interest Amounts in the form of 123,456.123 number format.

Account num, Trans ID, Interest
001, DC004074, 123456
002, DC004023, 4567789
001, DC004056, 876543
003, DC004023, 4567789

I need to work on these two conditions:

  1. If I find same account number in table then sum all interest for each of the deposit IDs.

  2. if I find 1 deposit ID but transfered into 2 different account then the interest should divide based on total number of accounts.

Output should be like this:

Account num, Trans ID, Interest
001, (DC004074,DC004056), 999999 ## 123456 + 876543
(002,003), DC004074, 228394.5 ## 4567789/2

Please help me with a solution, possibly LINQ query to extract the DC number and calculate the interest amount and get output. (I do not know LINQ actually) :sweat_smile:

I referred to this topic but this only solves half of my problem.

2

maybe better to temporary split the table into two groups

  • AddCaseGroups | List(Of List(Of DataRow)) - outer the groups, inner the members
(From d in dtData.AsEnumerable
Group d by k=d("Trans ID").toString.Trim into grp=Group
Where grp.Count = 1
Select gl = grp.ToList).ToList

Similar we can create the SplitCaseGroups
(From d in dtData.AsEnumerable
Group d by k=d(“Trans ID”).toString.Trim into grp=Group
Where grp.Count > 1
Select gl = grp.ToList).ToList

And finally we can combine both with the adequate aggregation need

3

Hi,

I am having difficulty understanding your suggestion.

But what I did was to try the solution that is provided in the attached link for the other post.

This Post

I am getting output with only the result (Sum) and not the entire Row (with Acc num and Trans ID) like:

Account num, Trans ID, Interest
123456
4567789

Below is my code:

(From d In op_DT
Group d By k=d("Account Num").toString.Trim Into grp=Group
Let sm = grp.Sum(Function (x) CDbl(x.item("Interest").toString.Trim))
Select op_DT.Rows.Add(sm)).CopyToDataTable

How to get all items from the row?

4

Where in detail is it not understood

5

@piyush.g

Use this Linq Query

(
From row In dt
Group row By a = row(“Column Name”).ToString.Trim Into grp = Group
Where grp.Count > 1
Select grp.ToList
).SelectMany(Function(x) x).CopyToDataTable

1 Like
6

Preparations:

grafik
grafik868×443 44.7 KB

Input:
grafik

Add Set:
grafik

(From d In dtData.AsEnumerable
Group d By k=d("TransID").toString.Trim Into grp=Group
Where grp.Count = 1
From g In grp
Group g By k2=g(0).toString.Trim Into grp2=Group
Let  til = String.Join(",",grp2.Select(Function (g2) g2(1)))
Let am = grp2.Sum(Function (g3) Convert.ToDouble(g3(2))).toString("F2")
Let ra = New Object(){grp2.First()(0), til, am}
Select r = dtAddSet.Rows.Add(ra)).CopyToDataTable

SplitSet
grafik

(From d In dtData.AsEnumerable
Group d By k=d("TransID").toString.Trim Into grp=Group
Where grp.Count > 1
Let  anl = String.Join(",",grp.Select(Function (g) g(0)))
Let ams = (Convert.ToDouble(grp.First()(2))/ grp.Count).toString("F2")
Let ra = New Object(){anl, k, ams}
Select r = dtSplitSet.Rows.Add(ra)).CopyToDataTable

With a merge Datatable Activity we can bring the AddSet and the SplitSet together in one datatable

We would recommend to do some more fine tuning, as this prototype was done as a quick show case

7

image

if have 2 deposits id but transfered into one account number then sum all interest from the 2 deposits id and put into the excel

*if have 1 deposits id but need to transfered into 2 different account then the interest divide based on total account number n and put into the excel
want these result fpr DC and ACCOUNTNO result grouping the column and sum interest witout chang excel format in linq