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

[Mr.H]

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()

[ppr (Peter Preuss)]

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

[ppr (Peter Preuss)]

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

[Mr.H]

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:
   

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

[ppr (Peter Preuss)]

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

[ppr (Peter Preuss)]

For output 1 following is imprtant but wa not answered

@ppr

Data should be grouped on item and location?

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

[Mr.H]

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

Thanks you very much!

[ppr (Peter Preuss)]

@Mr.H

Sorry, i will explain again to you as below.

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

[Mr.H]

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!

[ppr (Peter Preuss)]

@Mr.H

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”

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

[Mr.H]

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!