Iterative Macro limitation

[alexandra_hanna]

Hi Community,

I am trying to set up an iterative macro from a workflow which I have manually copy and pasted several rounds of the same repeating units:

SUMMARIZE - group by to get the total value for a reapeating date

JOIN - bring in this total

FORMULA - to divide the value for each row by its total value

The data looks something like this:

Date                          Value           Total Value (from Summarise)    New Fraction (from Formula)

12.02.03                         1                5                                                 1/5

12.02.03                         2                5                                                 2/5

12.02.03                         2                5                                                 2/5

15.02.03                         1                6                                                 1/6

15.02.03                         2                6                                                 2/6 

15.02.03                         3                6                                                 3/6

The new fraction becomes the inital Value and the process is repeated, creating a new Total Value. The reasoning behind using this type of macro over other is that the calcultion in the formula should be done on a row by row basis but it seems like you cannot extract the total values - as the iterative flows through one row at a time, how would you go about setting up this repeting workflow?

Finally there last step is to filter out thosee values that total=6.

Cheers,

Alexandra

[jdunkerley79]

Could you explain the termination point.

The second iteration would seem to be 0.2, 0.4, 0.4 for the 12-Feb

The total on the second iteration must be 1 so it would keep repeating?

[alexandra_hanna]

Yes, the values for the 12th would remain in the iteration loop. The termination is not too important because we are flexible to go either way reaching total of 6 as a condition or have a constant iteration number of 20 for example.

[jdunkerley79 · Accepted solution]

You can use a filter to remove those that had a total of 6 at each pass. 

I think however that as defined this will lock onto a total of 1 for each day (which wasnt 6 to start with) after first iteration then just keep repeating until the max iterations is met (20).

Have attached a sample macro and workflow doing what you described. It will iterate 20 times and if not converged add the results after 20 steps onto the output.

[alexandra_hanna]

Thank you James, it is a relief that my workflow looks identical to yours but somehow the results are not coming through the removed side and it is most likely the naming. I also did not know where you could specify the condition for the number of iterations.

Cheers,

Alexandra