Join Datatable Partial Match

Help Studio
1

Hi,

I need to join two datatable on partial match between columns for each table. The Join Data table activity does only = match. I need a partial match like in SQL +%Column1%+ = ‘Column2’

2

Unfortunately there is nothing like this :frowning:

You could use LINQ expression and “Contains” method instead

var names = from cust in customers 
            where cust.FirstName.ToLower().Contains("is")
            select cust;

google “LINQ partial match” for more info

Cheers

1 Like
3

(From d1 In AuditTable.AsEnumerable
Group Join d2 In FileSpecs.AsEnumerable On (“%”+d1.Field(Of String)(“AutomatedFileName”)+“%” ) Equals (“%”+d2.Field(Of String)(“FileName”)+“%” ) Into gj = Group
From g In gj.DefaultIfEmpty
Select ra = {d1(0), d1(1) ,If(isNothing(g), Nothing, g(1)) }
Select dtResult.Rows.Add(ra)).CopyToDataTable

This works. But instead of equals, I need something like contains.

4

@mrajaram
unfortunately there was no sample data for getting used to derive the requirements but let me try:

Your case looks like a

  • left join
  • a partial match to 1 Join column
  • retrieving another column from right side table if present
    • or empty/null

Indeed the Join syntay is very strict and requires the Equals and the use of left side / right side table etc.

In case of we are too restricted by this some statements allows to get rewritten by a cartesian product

given data:
dt1:

Column1 Column2
A#1A 1
B#2B 2
C#3C 3

dt2:

Column1 ColX
#1 VAL_#1-1
#2 VAL_#2-1
#2 VAL_#2-2
  • it is to look with the partial #X and should retrieve matching ColX or empty
  • #2 is testing the cartesian product as multiple matches are to return
  • #3 is testing left join empty match

Result:
Column1 Column2 ColX
A#1A 1 VAL_#1-1
B#2B 2 VAL_#2-1
B#2B 2 VAL_#2-2
C#3C 3

Statement:

(From d1 In dt1.AsEnumerable
 From colx In dt2.AsEnumerable.Where(Function (r) d1(0).toString.Contains(r(0).toString)).Select(Function (x) x("ColX").toString).DefaultIfEmpty("")
 Let ra = d1.ItemArray.Concat({colx}).ToArray
 Select dtResult.Rows.Add(ra)).CopyToDataTable

find demo xaml here:
LeftJoinSimulation_PartialMatch.xaml (10.9 KB)

1 Like