Remove rows where a column doesnt containt the elements in an array

Help Activities
, , ,
1

Hey everyone,

I have an array “MaterialArray” which has some material numbers inside it. And I have datatable DT.

This DT has a column named “Material Number” and there are material numbers written. I want to remove the rows where the “Material Number” column contains a value which is present in the Array.

Ex:
MaterialArray= [“A”,“B”,“C”,“D”]
DT

Material Number
A
B
C
D
E

After the execution of that code I want a dt:

ResultDT:

Material Number
E

so the rows which material array has the values are removed.

I wrote this code but It is not working properly.

(From r In DT.AsEnumerable
Where Not MaterialArray.Any(Function (x) r(“Material Number”).toString.toUpper.Contains(x.ToUpper))
Select r).CopyToDataTable

Any sugesstions?

2

Hi @jntrk ,

Just now checked your Linq expression it was working perfect. Not sure why you are getting issue. Please check how did you created your string array. thanks.

image
image1373×58 2.73 KB

3

I create the string array from another column of another datatable with this:

Catalogdt.AsEnumerable.Select(Function ( r ) r(“MTN”).toString).Distinct().toArray

so there are actually 1140 items in the array.

4

So the thing is when I write the linq expression as “where Not” i get the error that the source contains no dt, saying that there is no rows to the ouput but there is actually 1 row which has a material number that is not present in the array so actually it should return that row.

And when i say “where” it outputs the original DT… it weird

5

Hi @jntrk ,

Ok got it but not sure how the array look like. Please share us more details.

Based on your last response I understood that you are not getting any single row as output from your linq output. Correct me if I am wrong.

Try linq like below instead of contains match use equal

(From r In DT.AsEnumerable
Where Not MaterialArray.Any(Function (x) r(“Material Number”).toString.toUpper.Equal(x.ToUpper))
Select r).CopyToDataTable

6

Hi,

Extra whitespace might exist in Material Array. For now, can you try the following expression?

(From r In DT.AsEnumerable
Where Not MaterialArray.Any(Function (x) x.ToUpper.Contains(r("Material Number").toString.toUpper))
Select r).CopyToDataTable

Regards,