FIFO Iterative Macro - Solve for the Remainder

[jvalcareng002]

I have been working on an iterative macro to solve a FIFO logic scenario. The idea is that each quantity ordered is consumed by each quantity delivered in a time series order from the first quantity ordered (determined by the first invoice order date).

This workflow reconciles 2 inputs (1 for date / quantity ordered, and 2 for date / quantity delivered) based on customer id and product type ("New Item"), and it has three outputs as follows:

Output I: Quantity Ordered is greater than Quantity Delivered - The difference between Quantity Ordered and Quantity Delivered is positive. Output I is rerun through the iterative macro up until all iterations are run and the remaining excess of Quantity Ordered over Quantity Delivered is output.

Output O: FIFO Output: For all matches between the two inputs based on customer id and product type, the FIFO output includes the total Quantity Delivered up until the total Quantity Ordered is consumed. 

Output E: Quantity Ordered is less than Quantity Delivered - The difference between Quantity Ordered and Quantity Delivered is negative. This is the case where we have inadvertently sent more product than the customer ordered (not an ideal situation), or the customer id or product type do not match. 

Output E is the output that is giving me trouble. Currently, I am getting a partial remainder, but somewhere along the way some part of the total difference between quantity ordered and quantity delivered is not flowing through. This means that if there are 3 items in an order and 10 are delivered, then the 3 that are consumed are included in Output O, and the 7 remaining do not flow through.

Refer to the example below:

Input 1: 

customer_id	customer	New Item	Effective Rate	Invoice Order Week	Order Quantity
10024	Customer1329	At Home Test	119	12/28/2020	1
10024	Customer1329	At Home Test	119	1/25/2021	4

Input 2: 

customer_id	customer	New Item	Delivered Week	Quantity Delivered
10024	Customer1329	At Home Test	11/30/2020	1
10024	Customer1329	At Home Test	12/28/2020	1
10024	Customer1329	At Home Test	1/4/2021	1
10024	Customer1329	At Home Test	1/18/2021	1
10024	Customer1329	At Home Test	1/25/2021	2
10024	Customer1329	At Home Test	2/1/2021	3

Output O:

customer_id	customer	New Item	Invoice Order Week	Delivered Week	# From Order Created on This Date	Effective Rate
10024	Customer1329	At Home Test	12/28/2020	11/30/2020	1	119
10024	Customer1329	At Home Test	1/25/2021	12/28/2020	1	119
10024	Customer1329	At Home Test	1/25/2021	1/4/2021	1	119
10024	Customer1329	At Home Test	1/25/2021	1/18/2021	1	119
10024	Customer1329	At Home Test	1/25/2021	1/25/2021	1	119

Output E - output E should appear as shown below:

customer_id	customer	New Item	Delivered Week	# From Order Created on This Date
10024	Customer1329	At Home Test	1/25/2021	1
10024	Customer1329	At Home Test	2/1/2021	3

Output E - however, the result I am getting for output E is missing a record, as shown below:

customer_id	customer	New Item	Delivered Week	# From Order Created on This Date
10024	Customer1329	At Home Test	2/1/2021	3

I have attached the macro and an example workbook below to show the correct output. 

[EricWe]

Hi @jvalcareng002, 

It appears that a formula may be needed after the Join tool's R output anchor. It may be possible to calculate all ordered and delivered quantities with a set of formulas such as the examples pictured from a workflow attached to this training session: Advanced Macro Development. New columns are used to calculate if the demand at each location was fulfilled and if there is remaining supply or demand based on the starting inventory.