Looping through set of rows based on a condition

Hello guys,

I have a an input file which has a list of Buy and Sell transactions sorted as per the Transaction Date. Please note that the highlighted column 'QTY Remaining' is the output column and the values are the desired output.

The task I have is to traverse through each of the transaction and perform following operations :-

1) If the 'Transaction Type' is Buy then the 'Qty Remaining' should be the 'Qty Purchased'

2) If there are say 2 consecutive Buy transactions then the 'Qty Remaining' should be the addition of 'Qty Purchased' from the current Buy transaction and the 'Qty Purchased' from the previous Buy transaction

3) If the 'Transaction Type' is Sell and the preceding transaction is Buy and the 'Qty Remaining' should be 'Qty Sold' from the Sell transaction minus the 'Qty Purchased' from the Buy transaction from the previous row.

4) If the 'Transaction Type' is Sell and the preceding 2 transactions are Buys and the 'Qty Remaining' should be 'Qty Purchased' from the previous 2 Buy transactions minus 'Qty Sold' from the Sell transaction.

The Point is that everytime when we encounter a 'Sell' transaction, we need to loop through the previous 'Buy' transactions which do not have corresponding Sell transaction and deduct the amount based on each Buy.

testDataforForum.xlsx

Iterative Macro

Accepted answers

sanket277

Thanks a lot Nicholas. This solution works fine. I will try testing it further with more complex data and reach out if I have any further questions. Really appreciate your help!!

Just wanted to check with you if there is any way to add a "where" condition in the Multi-row formula, as in to perform a functionality like traverse back upto "n" rows until a certain criteria is met in order to make that "n" more dynamic. Also, I have been reading that the only alternative to "for" loop in Alteryx is use an Iterative Macro, how far is that correct? Let me know your inputs, thanks!!

Cheers,

Sanket

All comments

NicholasM

@sanket277

I think I understand what you are trying to get at. I believe it can be solved using a multi-row formula rather than an Iterative Macro. This tool will allow you too look at and preform formulas based of information in the rows previous. This allows us to keep a running total for consecutive "Buy" transactions, as well as preform the necessary subtraction when a "Sell" Transaction occurs.

Please see attached workflow for the configuration.

Multi-Row Transactions.yxmd

sanket277

Thank you very much Nicholas for getting back!!

This works fine but there is one more case where in there could be multiple 'Ignore' transactions between 2 'Buy' transactions as present in the attached spreadsheet. The Ignore transactions will not affect the amount as such. But then it affects the multi-row formula in terms of hardcoding Row - 1 or Row - n. Could you please help?

Thanks,

Sanket

testDataforForum.xlsx

NicholasM

@sanket277

Interesting, Here is the revised Multi-Row Formula. I had to add in the possibility of the Transaction Type being "Ignore". Like before, the formula will essentially keep a running total, adding up all the consecutive "Buy" transactions and subtracting "Sell". Attached is the workflow.

Multi-Row Transactions Revised.yxmd

sanket277

Thanks a lot Nicholas. This solution works fine. I will try testing it further with more complex data and reach out if I have any further questions. Really appreciate your help!!

Cheers,

Sanket

Quick Links

This months top contributors

AkimasaKajitani 387

mceleavey 383

mbarone 337

Hollingsworth 335

LanisC 335