Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.

Macro to iterte through two variables in a formula.

mranade
6 - Meteoroid

I Have one table as follows:

mranade_1-1606166851758.png

 

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.

 

mranade_2-1606167573475.png

 

 

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

 

Any ideas on how I can accomplish this task?

 

 

5 REPLIES 5
T_Willins
14 - Magnetar
14 - Magnetar

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.

 

WorkflowsWorkflows

 

Batch MacroBatch Macro

 

Results (not all fields shown)Results (not all fields shown)

 

 

ASMR
7 - Meteor

Could you please attach the .ymxc file for that batch macro you created? I'm trying to recreate it based off the screenshot and your description but I'm having some trouble. 

T_Willins
14 - Magnetar
14 - Magnetar

Hi @ASMR,

 

Thanks for letting me know my attachment wasn't there.  I have updated my initial reply with the workflow and macro attached.

mranade
6 - Meteoroid

Thank you for the solution. I am still just a beginner. I am trying to research and understand the solution you have provided. 

 

It works perfectly fine and as I want it to. I still want to understand it before I build it myself and implement it to my solution. 

 

Thanks a lot again!

mranade
6 - Meteoroid

@T_Willins 

 

Thanks for your help. After a lot of requirement changes here is what my macro looks like. I learnt a lot from you. Thanks again!

mranade_0-1610562784031.png

 

Labels