Select unique rows from the datatable and delete repeated rows with conditions

Help Studio
, ,
1

Hi team ,

In the attached excel
Join_table.xlsx (11.5 KB)

image
image1303×486 32.3 KB

I want to delete the rows in which the line ids are repeating in the below pattern -

I want to pick the first row with line id = 1
Then it should pick row with line id = 2 but it should be the next to next row with line id =2
Then it should pick the row with line id = 3 but it should be the next to next row with line id = 3

and rest repeating rows should be deleted

So in the final table I should get values that are marked in yellow .

Please help me in this since this is very critical for our project.

@ppr
@Yoichi

2

Hi @gokul1904 write this code to get unique value

ReadDT.DefaultView.ToTable(true,“col1”,“col3”…)

3

Hi @gokul1904 ,

Maybe we could create a Dictionary for this logic and then use it when Grouping the rows by LineID column for the Selection part.

Creation of Dictionary :

dictIndex = new Dictionary(Of String, Integer) from {{"1",0},{"2",1},{"3",2}}

Here, dictIndex is a variable of type Dictionary(Of String, Integer)

We could perform the Grouping of the rows based on LineID column and then select the row according to the dictionary corresponding values.

OutputDT = DT.AsEnumerable.GroupBy(Function(x)x("LineID").ToString).Select(Function(x)if(dictIndex.Keys.Contains(x(0)("LineID").ToString),x(dictIndex(x(0)("LineID").ToString)),x.First)).CopyToDatatable

Although there might be better approach for this, you could try and let us know if it works.

4

it looks like the case is still open

May we ask you to check the following requirement definition item:

  • it is supposed to take for 2: excel row 6 as it is the following 2 to the first occurrence (excel row 3)

But we don’t see the rules applied for the case 1
Excel row 2 vs. Excel row 5

so maybe a more clear rewriting the requirements with more specifics is needed. Also we would recommend to include the definition of handling the non duplicated ids