I want filter the data table and take unique data table

Help Studio
, ,
1

HI folks,

I have some doubt in the data table.
Requirement:
I need to filter the data table which contain 7 column and in that we need to check the unique and keep one in the unique.

Case with same “Account id” or “Division” or “Customer name” value column of the SQL application has been already processed on the same received date

example data table.

Name Account ID Division Date recived
A 123 AA 01-12-2024
B 234 AA 01-12-2024
B 123 AA 01-14-2024
D 123 BH 01-12-2024
D 123 BG 01-12-2024

Also I need duplicates also because i need to update those value in another excel

Please let me know how to work on that.

2

Hi @copy_writes

In which column we have to we have to check and get the unique values. Or you want to check all columns.

3

First we need to check date and then we need to check name and customer

4

Okay @copy_writes

→ Take Excel Process scope activity and insert the Use excel file activity inside of it.
→ Give the path of the excel file in Use excel file activity.
→ Inside Use excel file activity, use remove duplicates activity.
→ In Remove duplicates activity, Select compare individual columns option and give the columns where you want to remove duplicates.

Check the below workflow for better understanding,

_2024
_2024877×966 69.7 KB

Hope it helps!!

5

W are not using Advance activity we need to use the linq quires or any other excel activity
UiPath Version is 2022

6

For the above input what is the expected output… @copy_writes

7

Update the case as business exception as “Duplicate account found” if the case with same “Account id”, “Division” and “Customer name” column of the docuware application has been already processed on the same received date.

8

Hear Receive date ween Need to check example I gave below.
We have Account id”, “Division” and “Customer name” and “Date received”
Received date column is we need to check once we check other 3 column Account id”, “Division” and “Customer name” in this any one once is matched then that will consider as duplicate.

Input:
image

Output:
image

9

Hi @copy_writes

→ Read Range Workbook
image
Output-> dt_Input
→ Use below query in Assign activity:

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
→ Write Range Workbook
image

image
image854×580 84.8 KB

Sequence.xaml (8.2 KB)

Hope it helps!!

1 Like
10

Let me check Thanks for your valuable time

11

@copy_writes

If you find the solution for your query please mark my post as solution to close the loop orelse if you have any questions, let me know. I’m happy to help.

Happy Automation!!

12

Thanks for the update. I hope you understand in a wrong.

Scenario:
First we need to check the “Date received”, next we need to check any duplicate value is present in other column i mentioned above.

So you can check my output which i mentioned in the above screen capture.

13

var filteredData =

 from row in yourDataTable.AsEnumerable()
                   where row.Field<string>("AccountID") == accountIdToFilter ||
                         row.Field<string>("Division") == divisionToFilter ||
                         row.Field<string>("CustomerName") == customerNameToFilter ||
                         (row.Field<string>("CustomerName") == customerNameToFilter &&
                          row.Field<DateTime>("ReceiveDate") == receiveDateToFilter) ||
                         (row.Field<string>("AccountID") == accountIdToFilter &&
                          row.Field<DateTime>("ReceiveDate") == receiveDateToFilter) ||
                         (row.Field<string>("Division") == divisionToFilter &&
                          row.Field<DateTime>("ReceiveDate") == receiveDateToFilter)
                   select row;
14 
// Read the CSV file into a DataTable
string filePath = "C:\\path\\to\\input.csv";
DataTable dataTable = new DataTable();
using (var reader = new StreamReader(filePath))
{
    string[] headers = reader.ReadLine().Split(',');
    foreach (string header in headers)
    {
        dataTable.Columns.Add(header);
    }
    
    while (!reader.EndOfStream)
    {
        string[] rows = reader.ReadLine().Split(',');
        DataRow dataRow = dataTable.NewRow();
        for (int i = 0; i < headers.Length; i++)
        {
            dataRow[i] = rows[i];
        }
        dataTable.Rows.Add(dataRow);
    }
}

// Create two new DataTables for storing non-duplicate and duplicate rows
DataTable nonDuplicateDataTable = dataTable.Clone();
DataTable duplicateDataTable = dataTable.Clone();

// Iterate through each row in the DataTable
DataRow previousRow = null;
foreach (DataRow row in dataTable.Rows)
{
    // Check if the current row is a duplicate based on Account Number, Division, and Customer Name
    if (previousRow != null &&
        (row["Account Number"].ToString() == previousRow["Account Number"].ToString() ||
         row["Division"].ToString() == previousRow["Division"].ToString() ||
         row["Customer Name"].ToString() == previousRow["Customer Name"].ToString()))
    {
        // Duplicate found, add to duplicateDataTable
        duplicateDataTable.ImportRow(row);
    }
    else
    {
        // Non-duplicate, add to nonDuplicateDataTable
        nonDuplicateDataTable.ImportRow(row);
    }

    // Update previousRow for next iteration
    previousRow = row;
}

// Output non-duplicate and duplicate DataTables
nonDuplicateDataTable
duplicateDataTable