Creating a loop similar to first in first out

Question

Hi All,

I am trying to find a solution to a problem that I facing. Any help would be appreciated.

I have attached a workbook (with 2 tabs) containing sales and expired products returns data at a billing document level. The aim here is (on a first out first in basis) to map the date of sale, against the returns (for the same product and batch) to ascertain how long the material was in the market. For e.g.:

Sales

Document NumberLine NumberBill TypeSold to PartySKU codeBatchPlantBilling DateBilled QtyRev_Net Value112802135921F210440132014789F25001211282/12/201657837.19112802167220F210440132014789F25001211282/19/2016150235114.57115302686610F210399632014789F25001211532/19/20163047022.851128021838610F210440132014789F25001211282/22/20165078371.65

Returns

Document NumberLine NumberBill TypeSold to PartySKU codeBatchWERKS_IBilling DateBilled QtyRev_Net ValueInv Date 1156610101170ZREN10791432014789F25001211567/4/201757837.242/12/2016 1152610815150ZREN10399232014789F25001211527/5/2017914106.882/19/2016 1152610818410ZREN10404832014789F25001211527/5/201757837.262/19/2016 1132615104800ZREN10293532014789F25001211327/6/201711552.872/19/2016 1132615104820ZREN10293532014789F25001211327/6/20171015528.892/19/2016 112061036520ZREN10264232014789F25001211207/7/201757818.802/19/2016 115261086720ZREN10399232014789F25001211527/8/2017710972.162/19/2016 11206103921130ZREN10128732014789F25001211207/10/20171015637.352/19/2016 1145610783750ZREN10187132014789F25001211457/11/201757701.352/19/2016 112461427610ZREN10057432014789F25001211247/12/201711560.122/19/2016 11266142641720ZREN10138932014789F25001211267/12/201711560.112/19/2016 1126614278120ZREN10063132014789F25001211267/13/20171117161.432/19/2016 113261513240ZREN10978132014789F25001211327/13/20172031057.572/19/2016 11206104271640ZREN10397732014789F25001211207/14/20171015637.452/19/2016 1120610428860ZREN10128732014789F25001211207/14/20172843785.172/19/2016 11286157361620ZREN10472632014789F25001211287/15/201734702.252/19/2016 113661434620ZREN10043432014789F25001211367/15/20172132610.652/19/2016 11566101422600ZREN10460932014789F25001211567/15/201757837.202/19/2016 11566101423270ZREN10460932014789F25001211567/15/20171015674.452/19/2016 111161073030ZREN10044732014789F25001211117/19/201723134.862/19/2016 11546101641330ZREN10401432014789F25001211547/19/20171015674.262/19/2016 11366143771700ZREN10202032014789F25001211367/20/201711552.872/19/2016 1153610440220ZREN10337632014789F25001211537/21/201769404.372/19/20162/22/2016

In the above returns data, I need to map the sale date (invoice billing date) keeping the quantity in mind i.e. the first return of 5 units that was returned on 4-Jul-17 was sold 12-Feb-16 (refer inv date column in the returns data). here, the 5 units sold on 12-Feb-16 are fully nullified. Hence, the product was in the market for 508 days.

For the next 22 records - the return qty total tp 181 whereas the sales on 19-Feb-16 total to 180. For the 1 extra unit of returned qty, I require the date of 22-Feb-16. This 1 extra unit is the complication that I cannot figure a way around.

Request some help on this.

Sample sales and returns for working.xlsx

SeanAdams · Answer

Hey @lkaivan

it may be possible to tackle this using an iterative macro - iterative macros work by iterating through your data multiple times until there are no rows to work on and then it exits.

So, in this case you want to take invoice-amount - returned amount - for the rows where you have a covering quantity you return these, and the rows where you have excess returns you iterate again.

More info about how to do an iterative macro can be found here: https://community.alteryx.com/t5/Interactive-Lessons/tkb-p/interactive-lessons/label-name/Macros

If you still continue to have trouble - I'd recommend posting a mocked up workflow with this in, and hopefully the community can help to solve (as @TrevorS  mentioned, there's a good resource on how to post for fastest possible solution).

TrevorS · Answer

Hello @lkaivan

Thank you for your contribution to the Community!

To help make it easier for others to assist, it would be helpful to get some additional information on your process. Here's what we recommend you include in your reply:

* What Alteryx Version are you using?
* Do you have a Workflow showing what you have attempted so far?
* What does the data look like? (Beginning data vs. Final Output)

Additionally, these below posts may help you with getting started!
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Loop-or-something-similar/td-p/522352
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Help-Looping/td-p/665418
https://community.alteryx.com/t5/Alteryx-Designer-Discussions/First-In-First-Out-Calculation/td-p/673460

Posting for Fastest Possible Solution (https://community.alteryx.com/t5/Alteryx-Community-Resources/Posting-for-Fastest-Possible-Solution/ta-p/178481)

Thank you very much!
TrevorS