Iterative Macro Help - Output contains unwanted iteration

[pchong]

I am trying to use a macro to randomly select X items from a pool of thousand items.

(picking 10 as example)

Macro part:

Here is what I have at the beginning of the macro to update the number of item, and then check a few conditions to make sure the random 10 items picked match my set of criteria.

If the ErrorCount is zero, output the 10 items, otherwise loop to the beginning and iterate.

However, as you can see in the screenshot below, the output is 100 items (10 items for 10 iterations)

It supposed to produce only 10 items (first iterate that fit all criteria), or an error if the desired output can't be produced in 10 iterations.

This is my configuration:

It would be challenging to create anonymized data, so it is difficult to upload a copy of my macro.

I'm hoping someone could spot my mistake right away.

Appreciate any advice to fix/improve the macro.

Thank you!

[apathetichell]

Can you share your error methodology? Here's my take -- every iteration there are 10 records with errorcount = 0 and multiple other records with errorcount !=0. The records which don't have errorcount=0 trigger re-running of your macro. You macro runs the 10 times you've set - and each time - 10 records (as fed in through your numeric up-down are selected).

If this is correct you can fix this by adding a summarize tool to your errorcount computation value. you would select the minimum value (ie if there are no failures). You would append that field - and then test based upon the appended field. If this isn't what you want - let's figure out how you are testing a successful vs failed run.

[pchong]

@apathetichell , thanks for replying.

No, the final output will be only 10 items (it is either 10 records with errorcount = 0 or 10 records with errorcount != 0)

I randomly selected 10 items at the beginning of the macro.

I have included some steps before the output here :

I have a formula to check if my values are within my lower & upper boundaries. (those are my criteria)

If the values are within the limit then 0, otherwise 1.

The Summarize Tool will sum the errorcount. As long as any one of the criteria is not met, I want to repeat the process.

After summing errorcount, I append the count with my randomly picked 10 items. (highlighted in the screenshot above). 

So if errorcount = 0, that will be the ideal output, no further iteration needed.

but If errorcount != 0, at least one of my criteria is not met, loop and iterate.

To conclude, the output will be either 10 items or nothing.

Hope this clarifies.

[apathetichell]

what's going into your append data on the other side of the count tool? basically you are looping even when you think your workflow should end. Alteryx doesn't know that your workflow is over because you keep sending records back into the loop. In your case - the count records tool will always create a record - even if the value is 0. That means that your iterations will continue.

[pchong]

Hi @apathetichell ,

The two append are similar (appending errorcount to my 10 items).

This filter here is how I differentiate to output or iterate.

Upper Stream :

If there is no error, the Select Tool will remove the field "ErrorCount" and output, no further iteration required.

Lower Stream:

If there is error (ErrorCount = 0), nothing comes up from the False anchor. Count tool will be zero, append the field to my original input (with thousand items). Select Tool will remove the field "ErrorCount" so that the output format is similar to my original input. 

Basically telling Alteryx to rerun the macro with the exact same input

I guess I got your point. But how can I improve or change?

Technically, when ErrorCount = 0, shouldn't the macro stop looping?

[apathetichell · Accepted solution]

Can you drop the count records/append fields tool and just feed the false anchor into the select and then the iterative output? Not sure what you are using the count tool for. If you need to keep it - you can add another filter for errorcount !=0 prior to the select tool - this would prevent macro re-runs...

when errorcount = 0 you are triggering your correct output (That's good) - but your false anchor is sending no records to your count records which becomes 0. You now have a record to append to your original stream and reiterate your macro. You don't want that. You don't want the count records - or you want to test somewhere downstream if you've already hit your trigger.

[pchong]

Thank you @apathetichell .

I guess I didn't really understand the macro earlier.

One of the filter anchors has to be empty in order for it to work right?

My thought was too complicated and kind of messed it up.

Thanks! 

[apathetichell]

yup! one has to be empty (or you have to want to run for a set number of iterations). TBH - it was super close from the get-go. really broken iterative macros tend to either a) not have the correct outputs at all or b) (more likely) have schema drift where columns keep being added on successive runs. Your inclusion of the select tool prevents that - so this was mostly correct.