Join datatabels with contains

I am trying to join two tables with contains.
From example:
Client | Location
A | Upstairs Room 1
B | Room 2 Downstairs

Team | Mail
Upstairs |
Downstairs |

The output needs to be:
Client | Location | Mail
A | Upstairs Room 1 |
B | Room 2 Downstairs |

I already found some topcis about joining on with a contains but I can’t get it to work.
At this moment a have the following statement which only results in one of the two tables:

(From Table1 In Table1.AsEnumerable() From Table2 In Table2.AsEnumerable() Where Table1(“Location”).ToString().contains(Table2(“Team”).ToString()) Select Table1).ToArray().CopyToDatatable

Can anyone help me to get the joined result?
Thanks in advance.

Good afternoon to you!
Please show how you worked with Join Datatables. Perhaps you can send a screenshot?


prepare dt3 with a build datatable:
Columns to configure:Client,Location,Mail - dt3

then use an assign acitvity:
LHS: dt3

(From d in dt1.AsEnumerable
Let em = dt2.AsEnumerable.Where(Function (x) d("Location").toString.Contains(x("Team").toString)).Select(Function (x) x("Mail").toString).DefaultIfEmpty("").First()
Let ra = d.ItemArray.Append(em).toArray
Select r = dt3.Rows.Add(ra)).CopyToDataTable
1 Like

Thanks @ppr! This works for me.

I don’t use the Join Datatable activity. Just the statement I mentioned in an Assign.
The answer of Peter works but thanks for your effort!

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