Unable to use [Engine.IterationNumber] to validate in a formula tool.

Question

Hi,

I am new to Alteryx and learning to use "Iterative macro", Below is a simple iterative macro which generated 500 rows as new field then checking for the condition as shown below where in I am validating if the current new value is equal to [Engine.IterationNumber]+1 if true it should change the inputt field to [new] value. But in my case it is only working for 1st row and rest of the values are 9999.

Would anyone please let me know what am I doing wrong here.

Also please let me know how can I assign [Engine.IterationNumber] to a column as shown below. (I have tried using formula tool and assign the value as [Engine.IterationNumber] but it only shows 0 for all lines)

inputt[Engine.IterationNumber](iteration value)102132

Attached is my macro file. Thanks in advance!

Regards,

Suneeth.

test iter macro.yxmc

AngelosPachis · Accepted Answer

Hi @Suneeth ,

In order for your iterative macro to run and the engine iteration number to start counting, you should run the macro on a separate workflow. To do that, open a separate workflow tab, right-click on your canvas and select insert > macro > and select your macro ( you will see your macro there if the workflow of the macro is open in a separate tab. Otherwise you can click on browse and locate it wherever it's saved in your computer)

Even if you do that and try running the workflow, you will notice that your workflow takes too long to run and probably doesn't give you the result you're after. I will try to explain what happens in each iteration and hopefully you will be able to trace the flaw.

So in each iteration you generate 500 new rows per record.

In the first iteration, you had a single record so you generate 500 new. Your engine iteration number is 0, so your formula says :

If new=0+1 then 1 else 9999 endif.

That's the reason you get only a single record equal to 1 and all others are 9999

Your filter tool cannot find any value that's equal to 500 so everything falls out of the false output anchor which is your iterative macro output. That will load to the macro's input again, but this time instead of having a single record

you will have 500

On the second iteration (engine iteration number 1) for each one of the records in your input (500) you will generate another 500. That is 250000 records will come out of your generate rows tool. That will be consisted out of 500 1s and 249500 9999s as shown below

Then something will happen in that formula tool, but I'm sure that this is not what you want to achieve. 3rd iteration you will have 500^3 records = 125000000 so until you reach record 2000 that is your iteration limit it will probably be bye bye laptop.

AngelosPachis · Accepted Answer

If you are new to Alteryx, I would advise goin through the macro training

https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Macros

and especially the one about iterative macros

https://community.alteryx.com/t5/Interactive-Lessons/Creating-an-Iterative-Macro/ta-p/657925

Without wanting to sound discouraging, engineiterationnumber is a rather complicated topic when you start your journey in iterative macros. Happy to demo an example of how that workflow/macro should be structured in a different way if you can explain what you want to achieve with it in the first place.

Cheers,

Angelos

AngelosPachis · Accepted Answer

@Suneeth  I wasn't sure when you wanted the macro to stop, so I used a condition that checks the maximum number of input (in our case 500) and subtracts one so you get an iteration value for all records

Hope that will help you understand more things about iterative macros and engine iteration number, please let me know if you have any further questions.

Cheers,

Angelos

(PS : Attached you will find three files, a.yxmd which is a normal workflow, a .yxmc which is the macro within the workflow and a .yxzp which is a zipped file containing both the macro and the workflow. If you open the zip file, that will unzip itself in the directory when you save it and will create a folder with the macro and workflow in them.)