Macro to iterte through two variables in a formula.

mranade

I Have one table as follows:

I need a formula like

cap16z1 = Min( 16 nov, z1),

cap16z2 = Min(16 nov, z2), and so on till cap16z7

cap23z1 = Min(23 nov,z1) ,

cap23z2 = Min(23nov, z2) and so on till cap23z7.

I have three formulas that needs to iterate from z1 to z7 and also needs t iterate through 4 dates  say 16 nov, 23 nov, 30 nov, 1 dec. that comes up to total of 3*7*4 = 84 formulaes if written separately for each combination.

I tried creating a macro like below. But it overwrites my "cap" field data. and shows only 1 cap column instead of 7 for each date-zone combination.

I know java/C/C++/C# but not R and Python.  😞

Any ideas on how I can accomplish this task?

T_Willins

Hi @mranade,

Below I've shown two methods of solving this.  The bottom (in the yellow container) is a static solution.  It Transposes the data to reduce the number of formulas to 4, then uses a Cross Tab tool for each date to convert the data back to a horizontal format.  A Dynamic Rename tool updates each field with the correct date, then all the data is Joined back together.  For a single use this is an easy solution.

However, you likely want to run this more than once for different dates.  In this case a more complex workflow is required with a Batch macro.  You had originally identified this as an iterative process, but an iterative macro is good for cases where you need to run until a condition is true, while a batch macro is good for doing an identical process against several unique data sets until all data sets have been processed.  In your case data by date is not dependent on any other date, so a batch macro works well.   By building in some fun Alteryx tools into the workflow, this should work with other dates other than the four you listed, even if you have more or less dates and/or more or less zones as long as the zones are in the "z" + number format.  I not, then the Dynamic Select tools would need to be updated to correctly filter the date fields from the zone fields.  The macros is essentially doing the same process as the Static workflow, but looking at each date individually.  Don't be intimidated by all the Action tools - they are just updating the field names in the tools for each batch (date).  The data comes out of the macro in not the greatest format, but another quick Transpose/Filter out nulls/Cross Tab cleans up the data nicely.  I used a Union tool to bring in the original data after the macro, but before the Transpose/Filter/Cross Tab to keep the fields in the correct order for using other dates.  Using a Join tool would also work, but any new fields (new dates) would be at the right of the results instead of in the order per your input.

Let me know if you have questions.

Workflows

Batch Macro

Results (not all fields shown)

Iterate multiple variables.yxzp