Alteryx Designer

Find answers, ask questions, and share expertise about Alteryx Designer.
Register for the upcoming Live Community Q&A Session - and don't forget to submit your questions for @DeanS regarding the future role of analytics here.

Iterative Macro and Dynamic NumRow in Multi-Row Formula

Highlighted
5 - Atom

I have been trying to use iterative macro and multi-row formula for the following question. Since Alteryx doesn't have global variable or I don't know how to set the NumRow to a variable, I can't figure it out. Please help!!

 

I have list ike below:

DateBuy/SellPercentageReversal_Date
2019-02-01Buy0.5 
2019-02-11Sell0.3 
2019-02-14Sell0.1 
2019-02-16Buy1 

 

For each transaction, if the percentage in the next opposite buy/sell direction reaches at least 50% of this transaction, then set Reversal_Date of this transaction to be the date of that next transaction. For example, transaction #1 (Buy 0.5 @2019-02-01) is reversed on 2019-02-11 because transaction #2 (Sell 0.3 @2019-02-11) is 1)opposite of buy, 2)0.3 > 50% of 0.5.

 

Then if current transaction has the same buy/sell direction as the next transaction, aggregate the percentage until the next opposite transaction to check whether to reverse. For example, transaction #2 (Sell 0.3 @2019-02-11) is reversed on 2019-02-16 because: Transaction #3 is still a sell, so aggregate transaction #3 percentage, now become 0.4 for the check. Then buy 1 on 2019-02-16, since it's opposite direction of transaction #2, so we check whether 1 > 50% of 0.4 

 

Also note only aggregate forward, no backforward. For example, transaction #3 (Sell 0.1 @2019-02-14) is reversed on 2019-02-16 because: Transaction #4 (Buy 1 @2019-02-16) is a buy, and 1 > 50% of 0.1.

 

I'm currently thinking use iterative macro to check whether [Buy/Sell] == [row+iteration: Buy/Sell]. However, I don't know how to set a dynamic NumRow in multi-row formula for the NumRow equals iteration number, which will change after each iterative macro runs. Am I on the right track? Is there any solution to this question?

 

Highlighted
Alteryx
Alteryx

Hi @znie007 ,

 

I'm struggling to follow your logic without seeing it an example of it but to answer your two questions:

  • You can set a global variable in the Workflow Configuration window in the Constants section (click on the canvas whitespace and then onto the Workflow tab; press the plus to add a user defined constant)
  • In an iterative macro, a new constant is added which refers to the iteration number ([Engine.IterationNumber]) so you can build your logic using that.

Hope this helps and if you need some more guidance on your use case then please provide an example of what you're trying to achieve.

 

Luke

Highlighted
5 - Atom

Hello, Thanks a lot for replying! I've created two workflows (one iterative macro - main workflow to process data, one batch macro - to make dynamic number of row for multi-row formula). However my workflow does not iterate itself. I'm not sure which part has problem. Would you help me check please?

 

Labels