Creating a loop similar to first in first out

[lkaivan]

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 Number	Line Number	Bill Type	Sold to Party	SKU code	Batch	Plant	Billing Date	Billed Qty	Rev_Net Value
1128021359	21	F2	104401	32014789	F250012	1128	2/12/2016	5	7837.19
1128021672	20	F2	104401	32014789	F250012	1128	2/19/2016	150	235114.57
1153026866	10	F2	103996	32014789	F250012	1153	2/19/2016	30	47022.85
1128021838	610	F2	104401	32014789	F250012	1128	2/22/2016	50	78371.65

Returns

Document Number	Line Number	Bill Type	Sold to Party	SKU code	Batch	WERKS_I	Billing Date	Billed Qty	Rev_Net Value	Inv Date
1156610101	170	ZREN	107914	32014789	F250012	1156	7/4/2017	5	7837.24	2/12/2016
1152610815	150	ZREN	103992	32014789	F250012	1152	7/5/2017	9	14106.88	2/19/2016
1152610818	410	ZREN	104048	32014789	F250012	1152	7/5/2017	5	7837.26	2/19/2016
1132615104	800	ZREN	102935	32014789	F250012	1132	7/6/2017	1	1552.87	2/19/2016
1132615104	820	ZREN	102935	32014789	F250012	1132	7/6/2017	10	15528.89	2/19/2016
1120610365	20	ZREN	102642	32014789	F250012	1120	7/7/2017	5	7818.80	2/19/2016
1152610867	20	ZREN	103992	32014789	F250012	1152	7/8/2017	7	10972.16	2/19/2016
1120610392	1130	ZREN	101287	32014789	F250012	1120	7/10/2017	10	15637.35	2/19/2016
1145610783	750	ZREN	101871	32014789	F250012	1145	7/11/2017	5	7701.35	2/19/2016
1124614276	10	ZREN	100574	32014789	F250012	1124	7/12/2017	1	1560.12	2/19/2016
1126614264	1720	ZREN	101389	32014789	F250012	1126	7/12/2017	1	1560.11	2/19/2016
1126614278	120	ZREN	100631	32014789	F250012	1126	7/13/2017	11	17161.43	2/19/2016
1132615132	40	ZREN	109781	32014789	F250012	1132	7/13/2017	20	31057.57	2/19/2016
1120610427	1640	ZREN	103977	32014789	F250012	1120	7/14/2017	10	15637.45	2/19/2016
1120610428	860	ZREN	101287	32014789	F250012	1120	7/14/2017	28	43785.17	2/19/2016
1128615736	1620	ZREN	104726	32014789	F250012	1128	7/15/2017	3	4702.25	2/19/2016
1136614346	20	ZREN	100434	32014789	F250012	1136	7/15/2017	21	32610.65	2/19/2016
1156610142	2600	ZREN	104609	32014789	F250012	1156	7/15/2017	5	7837.20	2/19/2016
1156610142	3270	ZREN	104609	32014789	F250012	1156	7/15/2017	10	15674.45	2/19/2016
1111610730	30	ZREN	100447	32014789	F250012	1111	7/19/2017	2	3134.86	2/19/2016
1154610164	1330	ZREN	104014	32014789	F250012	1154	7/19/2017	10	15674.26	2/19/2016
1136614377	1700	ZREN	102020	32014789	F250012	1136	7/20/2017	1	1552.87	2/19/2016
1153610440	220	ZREN	103376	32014789	F250012	1153	7/21/2017	6	9404.37	2/19/2016	2/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.

[TrevorS]

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/67...

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

Thank you very much!
TrevorS

[SeanAdams]

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).