Filtering big data tables

Help Robot
, , , ,
1

I have two data tables,
you need to filter table 1 by column filter from table 2. I’ve already used a loop for each, but the database is very large. This option is not suitable, since in this case it filters the table for a too long time. Is there any other way to solve this problem without using a cycle for each?
It is necessary for the robot to write down all the filtered tables at once, and not to filter each row.
I also tried using Filter Data Table with “Contains”, but it doesn’t work

2

Hi,

Probably we can achieve it using LINQ (or Invoke Code). Can you share specific sample and expected result? It’s no problem if dummy data.

Regards,

1 Like
3

ok, I have two books, I need to filter Table 2 by column “NoDoc”, take the values ​​from table 1 - column 1 “Filtering Column”.
Table 1.xlsx (9.5 KB)
Table 2.xlsx (10.8 KB)

4

Hi,

Can you try the following sample?

img20211228-a1
img20211228-a1665×719 23.7 KB 

dtKeep = dt2.AsEnumerable.Where(Function(r) dt1.AsEnumerable.Select(Function(r2) r2("Filtering Column").ToString).Contains(r("NoDoc").ToString)).CopyToDataTable()

dtRemove = dt2.AsEnumerable.Where(Function(r) not dt1.AsEnumerable.Select(Function(r2) r2("Filtering Column").ToString).Contains(r("NoDoc").ToString)).CopyToDataTable()

Sample20211228-a1.zip (18.2 KB)

Note : This workflow outputs 2 datatable : one is to keep record which exists in table1, another is to remove record which exists in table1.

Hope this helps you.

Regards,

1 Like
5

@Yoichi
it works! thank you very much! You helped me a lot!!!

1 Like
6

@Yoichi I have one question. in my real data I need to use read range without headers, how then will the expression change if I use column index?
I mean this expression:
dtKeep = dt2.AsEnumerable.Where(Function(r) dt1.AsEnumerable.Select(Function(r2) r2(“Filtering Column”).ToString).Contains(r(“NoDoc”).ToString)).CopyToDataTable()

7

Hi,

Please modify “Filtering Column” and “NoDoc” to index number as the following, for example.

dtKeep = dt2.AsEnumerable.Where(Function(r) dt1.AsEnumerable.Select(Function(r2) r2(0).ToString).Contains(r(1).ToString)).CopyToDataTable()

Regards,

1 Like
8

Thank you very much!!!

1 Like
9

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