How to merge the duplicate values and unique values of same duplicate no

Help Studio
, ,
1

Hi,

I am getting stuck when trying to merge duplicate rows and also trying to get the unique values of duplicate rows and unique rows

here is an example of what I am trying to get:

Before

Before
Before720×418 93.7 KB

After

After
After720×418 5.44 KB

Any ideas?

2

Hi @smarthari1997

You can try using the group by statement as below in Assign

dtout = (From d In Dtin.AsEnumerable()
Group d By k=d("Ref").toString.Trim, k1=d("Bill").toString.Trim Into grp = Group
Let Amt = grp.Sum(function(x) CDBL(x("Amount").ToString.Trim))
Let dates = String.Join(",",grp.Select(function(x) x("Date").ToString))
Let ra = New Object(){k,k1,coun}
Select r = Dtout.Rows.Add(ra)).CopyToDataTable()

Dtin is the data you read from excel
DTOut is the data you get after grouping data with Ref and Bill and then get sum and concatenated dates

Use build datatable to give a 4 column structure to DTout

Let me know if you need any help.

cheers

3

Hi,

How about the following?

image
image748×475 19.1 KB 

dt =dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Ref").ToString,r("Bill").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Sum(Function(r) CDbl(r("Amount"))),String.Join(",",g.Select(Function(r) r("Date").ToString))},False)).OrderBy(Function(r) r("Ref").ToString).CopyToDataTable

Sample20221217-2aL.zip (2.8 KB)

Regards,

2 Likes
4

The date is appearing like this 11/11/2022 00:00:00,11/12/2022 00:00:00 but i need only date

5

Hi @smarthari1997

I have update the yoichi expression. Can you check out this?

dt =dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Ref").ToString,r("Bill").ToString)).Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Sum(Function(r) CDbl(r("Amount"))),String.Join(",",g.Select(Function(r) Cdate(r("Date").ToString("MM-dd-yyyy"))))},False)).OrderBy(Function(r) r("Ref").ToString).CopyToDataTable

Regards
Gokul

6

@smarthari1997

In place of x("Date").ToString() please use x("Date").ToString.Trim.Split({" "},stringsplitoptions.RemoveEmptyEntries)(0)

Cheers

1 Like
8

can you explain about that code? i cant get it!!

9

@smarthari1997

Here is the break down

dt.AsEnumerable.GroupBy(Function(r) Tuple.Create(r("Ref").ToString,r("Bill").ToString))

This will group the datatable rows on Ref and Bill and give groups of data

Select(Function(g) dt.Clone.LoadDataRow({g.Key.Item1,g.Key.Item2,g.Sum(Function(r) CDbl(r("Amount"))),String.Join(",",g.Select(Function(r) r("Date").ToString))},False))

(For each group)Now from the groups we are consolidating and selecting a row which contains items as
g.Key.Item1 - ‘Ref’ Column value
g.Key.Item2 - ‘Bill’ Column value
g.Sum(Function(r) CDbl(r("Amount"))) - In the group sum of the ‘Amount’
String.Join(",",g.Select(Function(r) r("Date").ToString)) - In the group ‘Date’ Column joined with comma(,)

False - Is to just say that not to accept the changes or not to add to dt instead create and return with structure of dt

OrderBy(Function(r) r("Ref").ToString).CopyToDataTable

This will order the returned rows on Ref column and then copy the data to datatable

Hope this helps

cheers

10

can you make clear about the false please?

11

@smarthari1997

For loaddatarow… if we want to load the data into dt then we give as true …when we only want to replicate a action like load but actually do not make any addition to the dt then we use false…

In this case…loaddatarow is used to create a row from the groups retrieved but we dont need it to be added to dt…we only want to select a row from the dt

Cheers

1 Like
12

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