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!!!!
Solved! Go to Solution.
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.
Actually, there is a simple solution for you.
In the JOIN tool configuration there are radio buttons:
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
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!
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!
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.
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