Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!

Alteryx Designer Desktop Discussions

Find answers, ask questions, and share expertise about Alteryx Designer Desktop and Intelligence Suite.
SOLVED

First In First Out Calculation

jvalcareng002
6 - Meteoroid

Hi - 

 

I have two tables with the following information: 

 

Table 1-

Customer NameTotal OrdersWeek Ordered
Customer17128/31/2020
Customer1111/9/2020
Customer1112/14/2020
Customer1481/11/2021
Customer21012/7/2020
Customer336/1/2020
Customer4251/4/2021
Customer5238/17/2020
Customer5109/7/2020
Customer668/3/2020

 

Table 2-

Customer NameTotal Orders ReceivedWeek Received
Customer124011/9/2020
Customer121/11/2021
Customer181/11/2021
Customer1181/11/2021
Customer171/18/2021
Customer141/18/2021
Customer1601/25/2021
Customer2112/28/2020
Customer528/17/2020
Customer518/24/2020
Customer528/24/2020
Customer528/24/2020
Customer518/31/2020
Customer529/7/2020
Customer519/14/2020
Customer539/14/2020
Customer519/21/2020
Customer529/21/2020
Customer519/21/2020
Customer519/28/2020
Customer519/28/2020
Customer5510/5/2020
Customer5110/12/2020
Customer628/3/2020
Customer618/10/2020
Customer618/10/2020

 

I want to use a First In First Out approach to know from which initial order the orders received are coming.

 

The resulting table will show-

 

Customer NameTotal Orders ReceivedWeek ReceivedWeek Ordered
Customer124011/9/20208/13/2020
Customer121/11/20218/13/2020
Customer181/11/20218/13/2020
Customer1181/11/20218/13/2020
Customer171/18/20218/13/2020
Customer141/18/20218/13/2020
Customer1211/25/20218/13/2020
Customer111/25/202111/9/2020
Customer111/25/202112/14/2020
Customer1371/25/20211/11/2021
Customer2112/28/202012/7/2020
Customer528/17/20208/17/2020
Customer518/24/20208/17/2020
Customer528/24/20208/17/2020
Customer528/24/20208/17/2020
Customer518/31/20208/17/2020
Customer529/7/20208/17/2020
Customer519/14/20208/17/2020
Customer539/14/20208/17/2020
Customer519/21/20208/17/2020
Customer529/21/20208/17/2020
Customer519/21/20208/17/2020
Customer519/28/20208/17/2020
Customer519/28/20208/17/2020
Customer5510/5/20208/17/2020
Customer5110/12/20208/17/2020
Customer628/3/20208/3/2020
Customer618/10/20208/3/2020
Customer618/10/20208/3/2020

 

The orders received are shown to come from the first order placed up until the total # of orders. Once the initial order amount is exceeded, the orders received come from the date of the next order placed, and so on and so forth. For example, the 60 orders received by Customer 1 on 1/25/21 will come from the order placed on 8/31/20 up until 300 orders in total have been received. The remaining orders received are then split accordingly up until the total order amount over the orders placed on 11/9/20, 12/14/20, and 1/11/21.

 

Please let me know if you can provide direction as to how to solve in Alteryx. I've played around with the multirow formula but haven't exactly gotten it to work.

 

Thanks!

3 REPLIES 3
demand_james
8 - Asteroid

Hi -

 

This is a pretty common problem, and its reasonably complex to solve.   If you dont need to allocate remainders than you can use the multi-row formula.    If you need to get an exact figure and split orders you have to use an iterative macro.   Below is a post with lots of good detail on how to do this.

 

FIFO Logic - Iterative Macro - Alteryx Community

 

The iterative macros can also get troublesome if you start having lots of data (100,000s or millions of rows).   However it works well for smaller applications.

 

Let me know if this helps or if you get further stuck

 

James

 

jvalcareng002
6 - Meteoroid

Thanks James - this was helpful. I was able to get it as far as shown in the attached workflow. However, I have 2 questions: 

for 

1) How can I get this to show only the Week Ordered for that order amount? Currently it shows multiple Week Ordered records for a single Order Received:

 

jvalcareng002_0-1611171792118.png

 

2) The Join is tool is currently joined on customer name. This appears to work well for this set of sample data. However, the data set I am adapting this for has thousands of records and is taking a long time run in order to match the customer names between the two sets. Do you have any recommendations to shortcut this process? 

 

jvalcareng002
6 - Meteoroid

It's also not actually splitting the orders. So in the initial example, the order placed on 1/25 by Customer1 for 60 units is not being attributed to the corresponding week ordered. 

Labels