Data table fined if number of ocurens of row cell in dt is greater then 1 linq

Help Activities
, ,
1

I have a data table with Names and Id’s I need add two rows that shows weather the name or Id have been used elsewhere in the data table and I need it to be efficient because I have around 3,000 rows. I tried this and UI path crashed.
(From in_row1 In dt.asEnumerable
Let name_arr = (From in_row2 In PTD_DT_out.asEnumerable
Where (in_row2(“First Name”).Equals(in_row1(“First Name”)) And (in_row2(“Last Name”)).Equals(in_row1(“Last Name”))) Select in_row2(“Last Name”)).ToArray
Let Dup_name = If((name_arr.count)>1,True,False)
Let ID_arr = (From in_row2 In PTD_DT_out.asEnumerable
Where in_row2(“Login ID”).Equals(in_row1(“Login ID”)) Select in_row2(“Login ID”)).ToArray
Let Dup_login = If((ID_arr.count)>1,True,False)
Let ra = New Object(){
in_row1(“First Name”),
in_row1(“Last Name”),
in_row1(“Login ID”),
Dup_name,
Dup_login}
Select nr= dt_temp.rows.add(ra)).CopyToDatatable

table, th, td { border:1px solid black; }
First Name Last Name ID dup Name dup ID
FNameA LNameA ID1
FNameA LNameA ID1
FNamveB LNamveB ID3
FNameC LNameC ID4
FNameD LNameC ID4
FNameE FNameA ID6
FNameD LNameC ID5
out put
First Name Last Name ID dup Name dup ID
FNameA LNameA ID1 True True
FNameA LNameA ID1 True True
FNamveB LNamveB ID3 False False
FNameC LNameC ID4 False True
FNameD LNameC ID4 True True
FNameE LNameA ID6 False False
FNameD LNameC ID5 True False
2

Hi @David_Ellis ,

Maybe we would need an explanation on the output to achieve.

Why does the Second row’s Dup Name and Dup ID are false ? Since there is a duplicate in the First row, it should also have been True.

Also the third row Dup ID is True, but we don’t see it repeated more than once.

3

I apologize for the bad data entry. I think it’s fixed. to further enplane the logic if there is any other row with the same first and last name dub name should be true and if there is any other row with the same ID dup ID should be true.

4

@David_Ellis ,

We can maybe try with the below Steps :

  1. As we would need the Duplicate Count of Name and ID and it is to be identified exclusively within the Data. We can find the Names which are duplicates separately into a list and the duplicate IDs into a Separate List.

To get Duplicate Names :

DupNames = DT.AsEnumerable.Select(Function(x)x("First Name").ToString+x("Last Name").ToString).GroupBy(Function(x)x).Where(Function(x)x.Count>1).Select(Function(x)x.Key).ToArray

Here, DT is the input data from Excel sheet read using Read Range activity and DupNames is a variable of type Array of String which will contain all the duplicate names present.

To get Duplicate IDs :

DupIds = DT.AsEnumerable.Select(Function(x)x("ID").ToString).GroupBy(Function(x)x).Where(Function(x)x.Count>1).Select(Function(x)x.Key).ToArray

DupIds is a variable of type Array of String and will contain the duplicate ID values.

Next we update the Datatable columns based on these findings using the below Expression :

DT = (From r In DT.AsEnumerable
Let IsDupName = DupNames.Contains(r("First Name").ToString+r("Last Name").ToString)
Let IsDupId = DupIds.Contains(r("ID").ToString)
Select DT.Clone.Rows.Add(r.ItemArray.Take(r.ItemArray.Count-2).ToArray.Concat({IsDupName.ToString,IsDupId.ToString}).ToArray)).CopyToDataTable

Let us know if you were not able to follow the above approach.

5

What does the x.Key do within the Select?

6

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