Removing of duplicate data on row based using 2 columns condition

Help Studio
, ,
1

in the given input we need to take consider Column A and Column C duplicate should be removed if the E column has success it should not be removed fail duplicates should be removed on A and C

Input data:-
GEMSAS_OF_IND_WIPRO 76 21 Blanket Releases FAIL
GEMSAS_OF_IND_WIPRO 76 22 Blanket Releases FAIL
GEMSAS_OF_IND_WIPRO 76 23 Blanket Releases Success
GEMSAS_OF_IND_WIPRO 76 24 Blanket Releases Success
GEMSAS_OF_IND_WIPRO 76 Blanket FAIL FAIL
GEMSAS_OF_IND_WIPRO 76 21 Blanket Releases Success
GEMSAS_OF_IND_WIPR0 76 22 Blanket Releases FAIL

Output should be in this way :-

GEMSAS_OF_IND_WIPRO 76 21 Blanket Releases Sucess
GEMSAS_OF_IND_WIPRO 76 22 Blanket Releases FAIL
GEMSAS_OF_IND_WIPRO 76 23 Blanket Releases Success
GEMSAS_OF_IND_WIPRO 76 24 Blanket Releases Success

i get data from excel Datatable i need code in linquery

2

Hi @saikumar_pullakandam

Can you try the following expression to filter table:

dtTest.AsEnumerable().GroupBy(Function(g) Tuple.Create(g(“A”).ToString,g(“C”).ToString)).Select(Function(s) dtTest.Clone.LoadDataRow(s.Last.ItemArray,False)).CopyToDataTable()

3

To remove duplicate rows based on columns A and C, while considering the condition in column E (whether the value is “Success” or “Fail”), you can use the following LINQ query in UiPath:

LINQ Query:

(From row In inputDataTable.AsEnumerable()
 Group row By key = New With {Key .A = row("A"), Key .C = row("C")} Into Group
 Let firstRow = Group.OrderByDescending(Function(r) r("E").ToString).FirstOrDefault()
 Select firstRow).CopyToDataTable()

Explanation:

  1. Group By: The rows are grouped by columns A and C.
  2. OrderByDescending: After grouping, we order the rows within each group by column E. By sorting E descending, rows with “Success” will come first, ensuring that the “Success” row is kept when there’s a duplicate.
  3. Select: The FirstOrDefault() function picks the first row for each group, ensuring that you don’t keep duplicates of the same combination of values in columns A and C when column E has a “Success” value.
  4. CopyToDataTable(): After the LINQ query, we use CopyToDataTable() to convert the result back into a DataTable.

Expected Output:

A B C D E
GEMSAS_OF_IND_WIPRO 76 21 Blanket Releases Success
GEMSAS_OF_IND_WIPRO 76 22 Blanket Releases Fail
GEMSAS_OF_IND_WIPRO 76 23 Blanket Releases Success
GEMSAS_OF_IND_WIPRO 76 24 Blanket Releases Success

This solution ensures that only the rows with the “Success” condition (when duplicated) are kept, and rows with “Fail” are removed as per your requirement.

Let me know if you need further assistance!