I want to remove duplicate rows from datatable by comparing single column

Help Studio
, , ,
1

image
I need to remove duplicate rows which is highlighted by comparing Column Name [claim No] from data table. Please help me to handle this.

2

@qbss, You can use “Remove Duplicate Rows” activity

1 Like
3

Use group by and select first element

Thanks
@qbss

1 Like
4

It compare all the columns in rows. I need to remove duplicate rows based [Claim No] Column alone

5

Please update the query.

6

@qbss, In that case, you have to loop through the table and use “Remove Data Row” activity within.

The following thread might help you to build logic around how to loop through and compare it with the previous value and delete row if you find it as duplicate.

1 Like
7

Query
(From p In Datatable_Name.AsEnumerable() Group By id=p(“Column_Name”).ToString into group Select group(0)).CopyToDatatable

Thanks
@qbss

8

image
It also return the one entry of duplicate record. In my scenario i want to remove all the duplicate records.

9

@qbss
Use,
newDatatable = oldDatatable.DefaultView.ToTable(true, “Claim_No”);
If you want to remove duplicate rows based on multiple columns, specify the column names separated by ‘,’
newDatatable = oldDatatable.DefaultView.ToTable(true, “Patient_Responsibility”);

Hope this helps!

3 Likes
10

My Input:
image

My Output should be like below,
image

Please help me.

11

Use this Query
newDatatable = oldDatatable.DefaultView.ToTable(true, “Claim_No”)

12

Already tried, this not working. It return one entry of duplicate rows.

13

@qbss, you can do something like this:

image

4 Likes
14

@qbss

  1. Lets consider your table as, dtTable

  2. First try getting all the duplicate rows,
    dtDuplicateTable = dtTable.AsEnumerable().GroupBy(Function(dr) dr.Field(Of String)(“Claim_No”)).Where(Function(g) g.Count() > 1).SelectMany(Function(g) g).ToList().CopyToDataTable

  3. Now compare the original table with duplicate table and exclude the duplicate rows from original table,
    dtFinalTable = dtTable.AsEnumerable().Except(dtDuplicateTable.AsEnumerable, DataRowComparer.Default).CopyToDataTable

P.S: Check for record existence before converting as CopyToDataTable while doing complete implementation. Otherwise, if there are no records found, the statement will through exception.

Let me know if this works for you.

2 Likes
15

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