LAE

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mtenzinger

I am trying to do an inner join between two data sets. I want to look for the last name from the first data set in the description of the second data set.
I tried using where '1: Last Name' == left('2:Name',strlen('1:Last Name')) but it is not returning any matches and I can see some.
Please let me know how I can correct this. Thanks!

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: stonysmith

That's not going to work. That would essentially require that every record on the lefthand side be tested against the righthand side, and the since the length of data being searched for is constantly changing, that could affect the sort order.

If the '2:Name' field is comma separated in the format "Last, First", then this might work:
LeftInputKey='1:LastName'
RightInputKey='2:Name'.split(",").getItem(0)

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

With large data sets Stony is right. When you are doing the matches will the match always be at the start of the description? Because that is what yours and Stony's expressions assume. If so, then you can reduce the number of matches that have to be searched by joining on the first letter of both names in the join expressions, left('Last Name',1) to left('Name',1)

If the name can be somewhere in the middle or at the end then maybe take Stony's idea of splitting a step further and using one or more "Expand From List" nodes to split the description on spaces, and additional nodes to split on any punctuation. Then you can do the joins on the item field or the trimmed version of the item field.

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: mtenzinger

So if I wanted to run a split node and split 'Name' based on spaces what would I put in the PredicateExpr?

Note: This was originally posted by an inactive account. Content was preserved by moving under an admin account.

Originally posted by: ejones

I think you mean the "Expand From List" node and you'd put " " in the Delimiter field, Name in the ListExpr field and some variable name in the ItemField. The split node does something different.