I just started scratching the surface of using macros and am trying to figure out how to best create an iterative macro where I take my join keys and . . .
The image below provides an idea of what I'm doing currently and I think an iterative macro may best suit. The issue I'm facing is figuring out the proper setup, have the macro iterate down and when iterating through to say a length of 8, 6, or 2, it joins based on the predefined 8, 6, or 2 length fields on the right anchor.
Any assistance with an example how to best set that up would be awesome and thanks again!
Solved! Go to Solution.
Sure, here is a some mock up dummy data that may assist.
Left Anchor would be something like this:
Hier |
ABC |
ABCD |
ABCDEFGH |
123ABC |
123XYZ |
890ABC |
567QRSTU |
5551234A |
TUVWXYZ1 |
While the Right Side I create predefined length fields using the LEFT formula like this:
Hier | Hier8 | Hier7 | Hier6 | Hier5 | Hier4 | Hier3 | Hier2 |
AB | AB | AB | AB | AB | AB | AB | AB |
ABCD | ABCD | ABCD | ABCD | ABCD | ABCD | ABC | AB |
ABCDEFGH | ABCDEFGH | ABCDEFG | ABCDEF | ABCDE | ABCD | ABC | AB |
123ABC | 123ABC | 123ABC | 123ABC | 123AB | 123A | 123 | 12 |
123XYZ | 123XYZ | 123XYZ | 123XYZ | 123XY | 123X | 123 | 12 |
890ABC | 890ABC | 890ABC | 890ABC | 890AB | 890A | 890 | 89 |
567QRSTU | 567QRSTU | 567QRST | 567QRS | 567QR | 567Q | 567 | 56 |
5551234 | 5551234 | 5551234 | 555123 | 55512 | 5551 | 555 | 55 |
TUVWX | TUVWX | TUVWX | TUVWX | TUVWX | TUVW | TUV | TU |
So with the data you can see that I'm scaling back the data points to try and match most if not all combinations on my left side to the right.
Does this work for you?
This is the iterative macro. This works if String Field (Hier) has max length of 8. If you need to do it for a larger field, change your inputs to have a larger string size (you can even put Select Tool before the Formula Tool).
The L Output is the Left Join final output of your workflow. The J output is the Union result Output.
See package (workflow and macro appended).
If you have any doubts let me know.
Cheers,
Ah that is awesome! I was on the right track but I just couldn't figure out the proper implementation of the iteration number field with the backwards counting and how it also ties with the Hier length. Thank you so much!