Iterative Macro help - Joining by field Lengths

[jmstampe]

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 . . .

1. Start with a length of 8 or LEFT([fieldname], 8) with my data on the left anchor
2. Join it to an already predefined length of 8 field (LEFT[fieldname], 8) on my right Anchor
3. What doesn't match on the left Anchor is then truncated to a length of 7 or LEFT([fieldname], 7) then joined to a predefined length of 7 field on the right anchor
4. These steps are repeated until the field length is 2, these leftovers are joined one last time and if there still some stragglers not joined they are then sent off to a browse tool for review while all the matches are then stacked (union) together. 

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!

Current process learning to convert to iterative macro

[Thableaus]

Hi @jmstampe 

Could you please provide some sample data to build the macro? Just to test it.

Cheers,

[jmstampe]

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.

[Thableaus · Accepted solution]

@jmstampe 

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,

[jmstampe]

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!