community
cancel
Showing results for 
Search instead for 
Did you mean: 

Alteryx designer Discussions

Find answers, ask questions, and share expertise about Alteryx Designer.
Upgrade Alteryx Designer in 10 Steps

Debating whether or not to upgrade to the latest version of Alteryx Designer?

LEARN MORE

Achieve loop functionality without macro

Atom

Hello Alteryx pros!

 

Does anyone know the most efficient way to program a loop in Alteryx besides using a macro?

 

I know it will require a multi-row formula, but I'm not sure how to incorporate the loop component as the amount needs to be calculated and rolled over on a yearly basis. Please advise on how to efficiently utilize tools (such as generate row and multi-row formula) to "loop" through data row by row (yearly)? Please see the picture and attachment for further detail regarding the scenario described below.

 

Scenario:

Column 1 creates Columns A-B-C

Column C needs to replace A

Calculate B-C

 

 

AlteryxByRow_Question.png

Alteryx Certified Partner
Alteryx Certified Partner

I advise you start looking at iterative macros as a subject, if you give the community a search then you should fine some examples, then you can ask further questions!

 

Ben

Atom

Ben,

 

Appreciate the tip! Perhaps I should highlight that using a macro is our "last resort option" due to the use case at hand.

 

I wanted to ask the community for alternative tool combinations that helped them achieve the same result.

 

Thanks,

Hannah

Aurora

Hi Hannah

 

I've been able to do this kind of thing in the past.  You transpose the data so that it's in year/NAME/VALUE format, where name is the of your original column and Value is cell at the intersection of row/column.  Lets assume you only have the start and deltas in this form

 

YearStartChangeEnd
2015105 
2016 -2 
2017 7 
2018etc  

 

Becomes

 

YearNameValue
2015Start10
2015Change5
2015End 
2016Start 
2016Change-2
2016End 
2017Start 
2017Change7
2017End 
   

 

 

 

The your mulitrow formula for [value] is of the form

If [Name] = "Start" then

   [Row-1:Value]

Elseif [Name] = "Change"

   [Value]

Else

  [Row-2:value] + [Row-2:value]

endif

 

to give you

 

YearNameValue
2015Start10
2015Change5
2015End15
2016Start15
2016Change-2
2016End13
2017Start13
2017Change7
2017End20
   

 

Make sure to handle the special case of the first row, since there is no previous one.

 

Then you do a Crosstab to bring back the original structure.

 

Now this solution is not very dynamic and changing column order in the original completely destroys it.  Macros are definitely a better solution

 

 

Dan

Labels