Parameter for iterating macro from 1 to n for filtering data set

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

Macros

Dynamic Processing

Accepted answers

Thableaus

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,

ThomasWF.yxzp

All comments

Thableaus

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,

thomas_h

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

Thableaus

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:

Workflow with Macro appended.

Cheers,

ThomasWF.yxzp

thomas_h

Hi Thableaus,

Works just fine - thanks a lot!

Cheers, Thomas

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703