Iterative macro with Dynamic Rename Tools

[Eva]

I am trying to write an iterative macro that creates a hierarchy based on a parent-child relationship table. Even though I see that the first iteration works as expected, it is throwing errors and not behaving the way it's supposed to. Can someone help me with this?

I have attached a basic workflow as well as the macro. Both files have extensive comments and explanations to what is happening in each part of the process.

Basically it is an iterative macro, that uses a parent-child table, to iteratively join in two new columns (id and name) to the hierarchy that is built from the root to each leaf, one level each iteration. Before the first join in each iteration, I use a Dynamic Rename Tool to rename the column that is used in the Join Tool. After the join, I rename the same columns to their original name as well as give the columns added in the join their correct names.

The error message suggests that there is some problem in the dynamic renaming since the error states that the column that I think I am naming before the join is actually missing. The error is introduced during iteration #2 and repeats until the macro stops at iteration #100.

When I look in the Dynamic Rename Tools in the macro, I also see that the iteration number looks to be unknown. Why is that?

Any help is appreciated, and please be kind, this is my first attempt at writing an Alteryx macro. :-)

[apathetichell]

try a workaround -create a column of the match logic in your right data - and then use dynamic rename itself as the Join - taking the names from your right data source when the left & right match whatever logic you have. Sorry - don't have time to fix it - but that would be my approach.

[PangHC · Accepted solution]

here my reply to your question. 

1) The macro complains about a field that is missing. I believe that I am making sure that the field is there by using Dynamic Rename Tool. Obviously it's not working like I thought it would.

No idea, but if this case happens, try copy and paste to new workflow. 

2) The Engine.IterationNumber is unknown in the macro. Why?

No idea, maybe no field is allowed? but workaround is via field + formula tools, (select tool to add length for field name)
at least formula tool takes iteration number.

3) The continue/stop criteria does not seem to work. I don't know what to do about that. Perhaps it will work once the other error is solved?

it due to you should use append. instead of union, for both. 

key is stop sending data to iteration output.

4) Perhaps there are other issues. I don't know.

nope.


the output: i not sure did it right or wrong.

[Eva]

Thank you @PangHC. The macro works now (after some updates, see below). 🙂

1. The problem with the macro missing a field that I specifically made sure was going to be there in the previous tool is still weird to me. But your solution uses a few other steps that seem to solve that.
2. I also thing that it is strange that an iterative macro cannot recognize the Engine.IterationNumber. That is something that is not defined by me. It's an Alteryx macro thing. Anyway, your Field Info Tool + Select Tool + Formula Tool seems to work. Thank you for that solution. I will dive into it and try to understand why it works.
3. Ok. Fair point. Well made. I'll use the Append Tool instead of the Union Tool. 👍 
4. When it comes to possible other issues that I was anticipating...
   1. There is one row of data missing in the final table. You can see that when comparing the actual result with the expected result image, record number 14 in the expected result is not present in the actual outcome. I have no idea why. I'll start troubleshooting that right away.
      SOLVED: It was my fault. I had steps in the macro that removed duplicates. That was the cause of the missing row in the output. I have since then removed those steps in the macro.
   2. I also need to rethink my stop criteria. The macro runs one iteration too much. I need to either find a way for it to stop a bit earlier or handle the two empty columns that appear from the final "empty" iteration.
      SOLVED: I opted to remove the empty columns outside of the macro.

I am soo happy with the huge progress with the macro. I now have a working solution. Thank you again @PangHC. 🤗