Filter datatable with values from other datatable

Help Robot
1

Hello all,
I’m searching for a solution for this filter-task:
I have datatable1 with two columns (‘name’ and ‘user-ID’) and datatable2 with a column ‘user-ID’ and some more columns.
I need a new datatable, that contains all rows from dt2, where the value in ‘user-ID’ is one of the values in dt1.user-ID.

I could iterate through dt2 with for each row and check if the value is in dt1, but dt2 has > 1 million rows. And I have dt3 with values in a column ‘Fkt-ID’ and these values are also for filtering in dt2, where I have the same column.

I tried to find a solution here in the forum, but I didn’t find one - maybe I used the wrong search expression.

If someone can help me with the expression for dt1 and dt2 I think I can build it with dt3 by myself.

Thank you!
Moritz

2

Hi @mm1904

Checkout this expression

DT2.AsEnumerable().Where(Function(row) DT1.AsEnumerable().Where(function(r) r("user-ID").ToString.Equals(row("user-ID").ToString)).Any).ToArray.CopyToDatatable

Regards
Sudharsan

1 Like
3

Hi @Sudharsan_Ka,

thanks for your answer. I get an error “Assign: The source does’t contain DataRows”.
I have checked dt1 and dt2, there are data inside:
grafik

grafik
grafik693×142 4.74 KB

grafik

Thanks!
Moritz

4

HI @mm1904

Check the Datatable manually and see whether you got any user id that are common ?

Also check whether it has some extra space there if yes try with this expression

DT2.AsEnumerable().Where(Function(row) DT1.AsEnumerable().Where(function(r) r("user-ID").ToString.Trim.Equals(row("user-ID").ToString.Trim)).Any).ToArray.CopyToDatatable

Regards
Sudharsan

5

Also i see the column name like
image

User-IDHeiko…

check whether you are using correct column names in your expression @mm1904

Regards
Sudharsan

6

Hi @Sudharsan_Ka,
the expression is case-sensitive? I Have ‘5zqs’ vs ‘5ZQS’.
I changed in dt2 the value from ‘5zqs’ to ‘5ZQS’ and now it works!
Can I add “.tolower” to get all matches?

And can I add a second (or third) condition with “and” in the expression? If yes, at which position?

Thanks for your help!
Moritz

7

I’ve seen this, but this is only in the studio, the dt is allright.

8

Yes you can @mm1904

DT2.AsEnumerable().Where(Function(row) DT1.AsEnumerable().Where(function(r) r("user-ID").ToString.Trim.ToUpper.Equals(row("user-ID").ToString.Trim.ToUpper)).Any).ToArray.CopyToDatatable

Regards
Sudharsan

9

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.