Remove duplicate row if same value at one column and another column confirm condition

Hi Everyone.
@ppr Bro

I have a input datatable as below. I want to check and remove the row if they have same value at “item” Col and the value at “status” Col equals “OK”

I have made linq code as below but it run not correctly.

Is there any wrong in my code? and how to fix it?

Thanks in advance!

(From row In Table.AsEnumerable()
Group row By k1= row(“item”).toString.Trim,k2= row(“status”).toString.Trim.Where(Function(g) g.ToString.Contains(“OK”)) Into grp=Group
Let r = grp.Last
Order By Array.IndexOf(Table.AsEnumerable.toArray,r)
Select r).CopyToDatatable()

image
image

will have alook on it soon and will come back. the where part on k2 looks suspicious

1 Like

please give us some more details

  • Data should be grouped on item and location?
  • quantitiy should be summed up

what is to do for the status column? Any other Requirments?
Is last table expected output? Otherwise share the expected out put sample with us. Thanks

1 Like

Hi Bro.

Thanks you very much.

Base on your question. If can i want make two output tables as:

  1. Only remove second or first row to received datatable as below:
    image

  2. Sumup quantity and remove the first row. The output table as below:
    image

Give a try on following for output 2

dtResult | DataTable = dtData.Clone

(From d in dtData.AsEnumerable
Group d By k1= row(“item”).toString.Trim into grp=Group
let s = grp.Sum(Function (x) CInt(x("quantitiy").toString.Trim))
Let r = grp.Last()
Order By Array.IndexOf(dtData.AsEnumerable.toArray,r)
let ra = new Object(){k1, s, r("Location"),r("status") }
Select dtResult.Rows.Add(ra)).CopyToDataTable

For output 1 following is imprtant but wa not answered

Give a try on following:

(From d in dtData.AsEnumerable
Group d By k1= row(“item”).toString.Trim into grp=Group
Let r = grp.Last()
Order By Array.IndexOf(dtData.AsEnumerable.toArray,r)
Select r).CopyToDataTable
1 Like

Hi Bro.

Sorry, i will explain again to you as below.

The original table as below :

the case1: I want to check and delete duplcate row ( keep last row ) if it match two condition

  1. The values at “item” Column are same
  2. The value at “status” Column contain/equals “OK”

Output Table:

the case2: Same with case1 but it will sumup value at “quantity” Column.

Output
image

Thanks you very much!

Ok will have a look on it. Clear requirements help always for better solution. Thats was the reason, why questions were asked above.

The requirement looks different can you please confirm?

Group data by item column
#C1 - If group members count = 1 - take member
#C2 - If group members count > 1 - filter out all members where status =/contains “OK”

out1 - take last remaining group member
Whats to do if #C2 Count = 0?

out2 - sum up #C1 / #C2 - other values take from last group member

1 Like

Hi Bro.

Firstly i want to count duplicate rows exist or not in datatable with two condition:

  1. The value at “item” Col are same
  2. The value at “status” Col equals “OK”

If count >=1 then it will make 2 two datatable as previous post.

You can filter status col, but the output i want it enough data as output table in case1, case2.

Thanks you very much!

can be used within an if check

(From d in dtData.AsEnumerable
Group d By k1= row(“item”).toString.Trim into grp=Group
Let chk = grp.Any(Function (x) x("status").toString.Contains("OK"))
Select chk).Any(Function (x) x)

returns true if there is 1 or more groups (key=item) where at least 1 member status contains ok

1 Like

Thanks you bro… i know how to check to known there is 1 or more group with condition as i said that.

But how to check to remove duplicate row ( keep last ) if it match the conditions:

  1. The “item” column same value and the “status” column contain/equals value “OK”
  2. Same as ‘1’ but will sumup the “quantity” col.

Output Table1:

Output Table2:

Thanks in advance!