Dynamic Select in Iterative Macro

[SR_MBR]

Dear community,

I am having troubles by using the Dynamic Select in an Iterative Macro. I need to adjust regional values with the corresponding national value for several variables. The variables have a consecutive number as suffix, therefore I want to compute the matching pairs of regional and national values by using the "Engine.IterationNumber". In order to dynamically compute the adjustment, I want to select the variable of the regional and of the national data via the Iterationnumber, rename both and compute the adjustment.

The first run of the macro works fine, but starting at the second iteration, the Dynamic Select tool is not selecting the matching variable which creates an emtpy output. I'm sure I miss something in my Macro, but I just cant find it. Can you find my mistake and help me to get this workflow running? Or if you have another idea how to handle this problem, please feel free to improve my workflow. It would just be great to understand why my current workflow is not working.

Best regards

Sebastian

PS: For reasons of confidentality I cannot use my real data, so in the attached files, there is just a random sample I put together.

[BenMoss · Accepted solution]

To be honest, I think you are waaaaaaaaaaaaaaaaaaay over complicating this.

When you want to perform the same process on a number of columns, the best thing to do is to transpose your data. You can then ensure you include the field which indicates which 'iteration' it is, in any aggregations and joins.

Check out this example on your data which I believe gets you where you want to be.

Ben

[SR_MBR]

Yeah I kinda figured, that my solution is not the easy way :D

Thanks Ben, your solution works perfect.

Best,

Sebastian