Need to sort duplicates by address and # in data table

Help Studio
, ,
1

I am trying to remove duplicates in a data table using the following assign activities of
OpDataTable = (From i In OpDataTable.AsEnumerable
Group i By s=i(“ADD 1”).toString.Trim.ToUpper Into grp=Group
Select s = grp.First()).CopyToDataTable

This is not getting the best results as it seems to be skipping a name in the data due to the address matching. My goal is to remove all the duplicates and be left with the lowest EFF DT for each name , NPI and location so Here is the raw data

image
image1313×491 17.1 KB

So my results should be this
1801009360 GELLER, BRETT M. 01/01/2013 - 1521 8TH AVE STE 101
1801009360 - GELLER BRETT 01/01/2013 - 1620 POND RD

But I am getting this So my guess is that I am approaching this wrong?

image
image1107×233 18.6 KB

1 Like
2

Hi @jeff.shubzda

Can you share the sample input and Output. Tell us based on which column you need to remove the duplicate

Regards
Gokul

3

HI @jeff.shubzda

How about this expression?

(From p in DT.Select() where( From q in DT.Select() where q("NLC").Equals(p("NLC")) Select q).ToArray.Count>1 Select p).ToArray.CopyToDataTable()

It will remove the duplicate based on single column

Note : you can pass the column name or Index instead of (“NLC”) Like (“Column name”).

Regards
Gokul

4

If you look at the data above what I have is duplicate records for one NPI - of the same name at the same location but with different EFF dates and they would like to sort the data and remove all the duplicates and only keep the least effective date for that grouping. I tried to do it if you look at the 2nd image, but that didn’t work so well . I only want one row of NPI, Name, address and least Effective date, my data is probably grabbing the 1st effective date.

5

Share it in the excel it would great with sample data

6

Sorry only a screen shot like above there is other data in there I can’t share.

7

Create a sample Input file. Don’t need to all the details.

8

Here is a small sample file in it you can see duplicates for one NPI only difference is date and address so we only want one of each address with the least effect date to be written to another sheet.
sampledata.xlsx (10.8 KB)

1 Like
9

Well I made a change OpDataTable.AsEnumerable().GroupBy(Function(a) Tuple.Create(a(“ADD 1”).ToString, a(“PRIMARY_NPI”).ToString)).Select(Function (b) b.First).CopyToDataTable() and that gave me all the data I needed except it gives me the 1st Effective date not sure I could change this to get me the lowest effective date.

1 Like
10

Hi @jeff.shubzda
Try this and let me know if that’s what you needed to achieve :slight_smile:
TableFilter.zip (43.1 KB)

11

Hi @jeff.shubzda please tey the datatable.defaultview.totable(true)

It will give you only distinct values

12

Hi @jeff.shubzda
Did you achieve the filtering results?

1 Like
13

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