Hello,
I am currently trying to write a macro that automatically calculates totals for the positions from a list of balances. First the sub-totals have to be formed, then the totals below. This is what the column Level is for.
I am quite new in macro programming with Alteryx. How can I tell a macro to run from 1 to 10 and filter in each iteration the current level and form the sum?
I assume the macro will take a parameter "number of iterations".
Thanks for your hints!
Best regards,
Thomas
Acc # | Acc Name | Level | Amount | SumPosition |
123 | A | 1 | 10.00 | 345 |
234 | B | 1 | 20.00 | 345 |
345 | C | 2 | [calculated in macro iteration 1] 30.00 | 567 |
456 | D | 2 | 30.00 | 567 |
567 | E | [calculated in macro iteration 2] 60.00 |
Solved! Go to Solution.
Hi @thomas_h
How does your original dataset look like?
Can I assume the column amount is Null for levels below? And then, after the iterations, it gets filled up?
Cheers,
Hi Thableaus,
Each position which has a value in "SumPosition" has an amount (at least zero).
The Account numbers which are mentioned in the "SumPosition" column does not have an amount until they get filled up within their iteration.
Cheers,
Thomas
Hey @thomas_h
I came up with a solution, but I'm not so sure if it fits in your situation. Check it out:
This is the Iterative Macro that is going to fill up the Blank (or Null) values.
Watch out because the field "Amount" needs to be a Double Type for this to work. So, no letters can be within it.
- Add a Record ID
- Filter Level according to Iteration (Iteration starts at 0. So I always add 1).
- Summarize the amount of that level grouping by SumPosition.
- Join the value summarized to your Dataset. That's basically how you're going to replace the Null Value of Amount with your Summarized calculation.
See the configuration of Join tool to understand how I use Union (to Right Join) to put everything back together
- Sort Record ID to put everything in order
- Use Summarize Tool to get the Max Level Value. This is important to lead how your iterations are going to be handled.
- Append this Max Level Value to the dataset.
- Then you will have two paths:
1) It's the final Output (O). The final Output only will be populated when Max Level -1 is equal to your Iteration Number. That means you have to stop one iteration before the Max Level, because after all you won't need to sum amounts when you populate your last level.
2) It's the Loop Output. The Loop Output is going to be the Input of the next Iteration. So after you do all the steps (transformations) in one iteration, you're gonna go the next one. The Input received will be the Loop Output of the previous iteration. The Loop Output stops when there are no records on it. That's why the Filter Tool regulates this by the condition (Max Level - 1 has to be greater than the current Iteration). When it reaches 9 (out of 10), for example, it's false, so that tells the macro to stop.
I'm terrible explaining what I do, but if you have any doubts let me know. I recommend you to dive in the Macro, check the Interface Designer tab (when you're inside the macro). Iterative Macro is not so easy to understand at first, but once you get it, it comes in handy.
Workflow with Macro appended.
Cheers,
Hi Thableaus,
Works just fine - thanks a lot!
Cheers, Thomas