Join datatabels with contains

Help Activities
, , ,
#1

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

Table2:
Team | Mail
Upstairs | upstairs@test.com
Downstairs | downstairs@test.com

The output needs to be:
Client | Location | Mail
A | Upstairs Room 1 | upstairs@test.com
B | Room 2 Downstairs | downstairs@test.com

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.

#2

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

#3

@JanWillem

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

then use an assign acitvity:
LHS: dt3
RHS:

(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
#4

Thanks @ppr! This works for me.

#5

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!

#6

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