DataTable: Get the Unique datatable and Dupliacte datatable

Help Activities
, , ,
1

HI Team,

I need to filter the data table,
Input data have “Account id” “Division” “Customer name” “Received date”

I want to filter the data table for the below condition.

Condition: Received date we need to check first is there any duplicate are present are not if the received date is duplicate we need to check “Account id” or “Division” or “Customer name” or “Received date” any one of the column contain duplicate value then we need to remove from that.

Example input I gave below:

image

Expected Output:

image

And I need duplicate values in the data table.

Thanks,
Chethan P

2

@copy_writes

filteredDataTable = (From row In inputDataTable.AsEnumerable()
                     Group row By key = New With {
                         .ReceivedDate = row.Field(Of DateTime)("Received date")
                     } Into Group
                     Where Group.Count() = 1
                     Select Group.First()).CopyToDataTable()
3

I got error in this is there you mis any thing?

4

Hi @copy_writes

Check this:

resultDT = dataTable.AsEnumerable().GroupBy(Function(row) New With {
        .ReceivedDate = row("Received date"),
        .AccountId = row("Account id"),
        .Division = row("Division"),
        .CustomerName = row("Customer name")
    }).Where(Function(Group) Group.Count() > 1).SelectMany(Function(Group) Group).CopyToDataTable()
5

@copy_writes

check above thread

6

Hi @copy_writes

Can you try this

(From row In InputDT.AsEnumerable()
                     Group row By key = New With {Key .ReceivedDate = row.Field(Of String)("Received Date")}
                     Into grp = Group
                     Where grp.Count() = 1
                     Select grp.FirstOrDefault()).CopyToDataTable()

Cheers!!

7

Hi @copy_writes

Try below expression

DT.AsEnumerable().GroupBy(Function(row) New With {.ReceiveDate = row.Field(Of String)("Receive Date"), .CustomerName = row.Field(Of String)("Customer name"), .Division = row.Field(Of String)("Division"), .AccountID = row.Field(Of String)("Account ID")}).Where(Function(group) group.Count() > 1).SelectMany(Function(group) group).CopyToDataTable()

Happy Automation :slight_smile:

8

@copy_writes

FilterDatatable=(From row In Dt.AsEnumerable()
                     Group row By key = row.Field(Of String)("Your column name") Into Group
                     Let firstRow = Group.First()
                     Select firstRow).CopyToDataTable()

row.Field(Of String)(“Your column name”) give the datatype of the column in your excel if it is string give string or it is datetime give datetime

9

I Got the compiler error for code I got like "expected ‘)’ "

image

10

No This is not working

11

No this also not working can you check from your end for the above data table

12

@copy_writes

What is the error you are getting

13

Hi @copy_writes

dt_Output= dt_Input.AsEnumerable() _
    .GroupBy(Function(row) New With {
        Key .CustomerName = row.Field(Of String)("Customer Name"),
        Key .Division = row.Field(Of String)("Division"),
        Key .AccountID = row.Field(Of Double)("Account ID").ToString()
    }) _
    .Where(Function(Group) Group.Count() > 1) _
    .SelectMany(Function(Group) Group) _
    .CopyToDataTable()

dt_Output is of DataType System.Data.DataTable

image
image1102×722 28.8 KB

Hope it helps!!

14

Filter only not doing same result I am getting

15

Is that Code contain “_” ?

16

Hi @copy_writes

Try this:

dt_Output= dt_Input.AsEnumerable().GroupBy(Function(row) New With {
        Key .CustomerName = row.Field(Of String)("Customer Name"),
        Key .Division = row.Field(Of String)("Division"),
        Key .AccountID = row.Field(Of Double)("Account ID").ToString()
    }).Where(Function(Group) Group.Count() > 1).SelectMany(Function(Group) Group).CopyToDataTable()

Regards

17

I got The below error

image
image855×287 35.6 KB

18

@Parvathy @lrtetala @Krishna_Raj @rlgandu @Nawazish_Ahmad @supriya117

Thanks For your solution. but the solution you are given is not working.

Is there any other way we can do this using excel scope activity.

And also I need those duplicate that we filter from the data table.

19

Hi @copy_writes

Check out the below xaml file:
Sequence.xaml (8.3 KB)

Output:
Input.xlsx (9.3 KB)

Note: Sheet1 is input and Sheet2 is the output

Hope it helps!!

20

Thanks for your time can you please screen capture because I dont have UiPath application in this system.