Is possible to make a join with condition/function?

[Krasus]

Hello,

I know the join tool will take exacte the same chain to join!

Right now i wanna join my number with another column which contains only left(n) number to join.

For example, i have my number:

Number

123456

654321

And another table with the column key number (we can not use left() because the longer of keys are different all time):

Key

123

72164

6543

So finally we will get:

Number	Key
123456	123
654321	6543
null	72164

Thanks a lot!!!!

[JohnJPS]

If you're really just lining them up row by row, you could do a RecordID on both, then join on the RecordID.

Edit: or better yet, see the posts below.

[MarqueeCrew]

Actually, there is a simple solution for you.

In the JOIN tool configuration there are radio buttons:

• Join by Record Position
• Join by Specific Fields

The default is the 2nd option.  Change this to the first option and then UNION the data from L, J, R and allow for output of all fields.  You'll get your result I'm sure.

Thanks,

Mark

[jdunkerley79]

Or even easier...

Use a Join Multiple with the Join by Record Position

Union is for free :)

[Krasus]

Thanks for your answer!

But i think i explain badly! It's not a simple question about record ID.

Because in normal join tool, if we have 123 in the column "key", Alteryx will join only we have 123 in the column "Number". 

But right now, if i have a key 123, than i will join all the number start with 123 for example in my table i have 123456, so 123456 will join 123 together.

That's the same reason why i want 6543 to join with 654321.

At fitst i tried to have a left(3) to select only 3 numbers of my column "Number" before joining with the column "key", but the longer is not fixed in "key" some time i get 3 numbers, 4 numbers or even more. That's why i want to know if there is a way to make a join conditional.

Thanks a agin for your kindly help!

[Krasus]

Thanks for your answer!

It is the same problem, i think i explain badly! It's not a simple question about record ID.

Because in normal join tool, if we have 123 in the column "key", Alteryx will join only we have 123 in the column "Number". 

But right now, if i have a key 123, than i will join all the number start with 123 for example in my table i have 123456, so 123456 will join 123 together.

That's the same reason why i want 6543 to join with 654321.

Thanks a agin for your kindly help!

[MarqueeCrew]

How big is this data?

If the data in the lookup file was say 32,000 records or smaller, then you  might want to use a FIND & REPLACE tool.

Then I would assing record IDs (say 10,000 + for one and 1,000,000 for the other) and use a find & replace to search as:

* Beginning of Field

You can append the field & Record ID.

In your data, there could be exact matches and multiple matches.  I was appending the Record ID as a way to see which records matched where and how many times.  Maybe the ID isn't needed, but I'd test using it.

[jdunkerley79 · Accepted solution]

Stealing an idea from another answer on the community try this one.

Basically creates a set of keys of different lengths for the number and tries to join to the key.

Twaeked so Number only matched once (and matched to longest key).

[KaneG]

Another Option is to use get a join that covers all matches and then filter down, this is how I have done mass conditional joins before. So in this situation, you might create a field called [LNumber] that is the first digit of [Number] and then the same for [Key], then join on those followed by a filter for "[Key] = Left([Number],Length([Key]))".

Module attached.

Keep in mind that you will need to change field types to Strings and union on your unmatched data as well. 

Kane