How to retain rows from Datatable which does not contain any of the variables in an array

Help Studio
,
1

Hi,

I am trying to select data from a datatable (dt1) which does not contain any of the values stored as an array in another datatable (dt2).

Sample data for dt1
Invoice No| Name
001 | Sharon Ng Tin Lee
002 | David Chan
003 | Quek Boon Hui

Sample data for dt2
Name (assigned as “str_Name”) | Possible Names (assigned as “str_QueryName”)
Sharon | Sharon Ng, Tin Lee
David | David Chan
Boon Hui | Boon Hui

Assign arr_QueryName = str_QueryName.Split(","c)

Using a for each row in dt2,

Assign Results = (From d In dt1.AsEnumerable
Where arr_QueryName.Any(Function (x) Not d(1).toString.Contains(x))
Select r = d).CopyToDataTable

It works when there is only 1 value (i.e. for David and Boon Hui - the rows containing “David Chan” and “Boon Hui” are removed), but doesn’t work when there are two values (i.e. for Sharon - the rows containing “Sharon Ng”, “Tin Lee” are still in the datatable)

Anyone can assist? Thanks!

2

@Vrynzz

Can you please show the expected output as well…and we have to do a row to row comparision that is first row in one table to first row in another table?

Or is it like it can be there in any row?

Cheers

3

Hi, sorry let me do up another example to be clearer…

Sample data for dt1
Invoice No| Name
001 | Sharon Ng
002 | Ng Tin Lee
003 | Quek Boon Hui
004 | David Chan

Sample data for dt2
Name (assigned as “str_Name”) | Possible Names (assigned as “str_QueryName”)
Sharon | Sharon Ng, Tin Lee
David | David Chan

In the end I would only like to retain this:

Invoice No| Name
003 | Quek Boon Hui

Hope this explains. Thanks!

4

@Vrynzz

Please try this

Remove the for loop on dt2

arr_QueryName = String.Join(",",dt2.AsEnumerable.Select(function(x) x("PossibleNames").ToString).ToArray).Split(","c)

next

Results = (From d In dt1.AsEnumerable
Where Not arr_QueryName.Any(Function(x) d(1).toString.ToLower.Trim.Contains(x.ToLower.Trim))
Select r = d).CopyToDataTable

cheers

5

we assume that multiple names are present as a flatten string, seperated by ", "

with some risk (e.g. wrong partial matches like Chris, Christian matching) we can do

Assign Activity
dtResult =
(From d1 in dt1.AsEnumerable
let an = d1(“Name”).toString.ToUpper.Trim
Where Not dt2.AsEnumerable.Any(Function (d2) d(“Name”).ToString.ToUpper.Contains(an))
Select r =d1).CopyToDataTable

we ommited the string.split as we did a String.Contains Method check

When we want to mitigate the risk of wrong part matches we can do

using a more strict regex pattern and Regex.IsMatch
Or

Assign Activity
arrD2NamesSplits =

dt2.AsEnumerable.SelectMany(Function (x) x("Name").toString.Split(","c)).Select(Function (x) x.ToUpper.Trim).toArray

Assign Activity
dtResult

(From d1 in dt1.AsEnumerable
let an = d1("Name").toString.ToUpper.Trim
Where Not arrD2NameSplits.Contains(an)
Select r =d1).CopyToDataTable

Handling empty filter result:
:ambulance: :sos: [FirstAid] Handling of The source contains no DataRows exception - News / Tutorials - UiPath Community Forum

6

Thanks all!

I realise my error… I was using “Where […] Not…” when I should be using “Where Not […]”