Hi -
I have two tables with the following information:
Table 1-
Customer Name | Total Orders | Week Ordered |
Customer1 | 712 | 8/31/2020 |
Customer1 | 1 | 11/9/2020 |
Customer1 | 1 | 12/14/2020 |
Customer1 | 48 | 1/11/2021 |
Customer2 | 10 | 12/7/2020 |
Customer3 | 3 | 6/1/2020 |
Customer4 | 25 | 1/4/2021 |
Customer5 | 23 | 8/17/2020 |
Customer5 | 10 | 9/7/2020 |
Customer6 | 6 | 8/3/2020 |
Table 2-
Customer Name | Total Orders Received | Week Received |
Customer1 | 240 | 11/9/2020 |
Customer1 | 2 | 1/11/2021 |
Customer1 | 8 | 1/11/2021 |
Customer1 | 18 | 1/11/2021 |
Customer1 | 7 | 1/18/2021 |
Customer1 | 4 | 1/18/2021 |
Customer1 | 60 | 1/25/2021 |
Customer2 | 1 | 12/28/2020 |
Customer5 | 2 | 8/17/2020 |
Customer5 | 1 | 8/24/2020 |
Customer5 | 2 | 8/24/2020 |
Customer5 | 2 | 8/24/2020 |
Customer5 | 1 | 8/31/2020 |
Customer5 | 2 | 9/7/2020 |
Customer5 | 1 | 9/14/2020 |
Customer5 | 3 | 9/14/2020 |
Customer5 | 1 | 9/21/2020 |
Customer5 | 2 | 9/21/2020 |
Customer5 | 1 | 9/21/2020 |
Customer5 | 1 | 9/28/2020 |
Customer5 | 1 | 9/28/2020 |
Customer5 | 5 | 10/5/2020 |
Customer5 | 1 | 10/12/2020 |
Customer6 | 2 | 8/3/2020 |
Customer6 | 1 | 8/10/2020 |
Customer6 | 1 | 8/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 Name | Total Orders Received | Week Received | Week Ordered |
Customer1 | 240 | 11/9/2020 | 8/13/2020 |
Customer1 | 2 | 1/11/2021 | 8/13/2020 |
Customer1 | 8 | 1/11/2021 | 8/13/2020 |
Customer1 | 18 | 1/11/2021 | 8/13/2020 |
Customer1 | 7 | 1/18/2021 | 8/13/2020 |
Customer1 | 4 | 1/18/2021 | 8/13/2020 |
Customer1 | 21 | 1/25/2021 | 8/13/2020 |
Customer1 | 1 | 1/25/2021 | 11/9/2020 |
Customer1 | 1 | 1/25/2021 | 12/14/2020 |
Customer1 | 37 | 1/25/2021 | 1/11/2021 |
Customer2 | 1 | 12/28/2020 | 12/7/2020 |
Customer5 | 2 | 8/17/2020 | 8/17/2020 |
Customer5 | 1 | 8/24/2020 | 8/17/2020 |
Customer5 | 2 | 8/24/2020 | 8/17/2020 |
Customer5 | 2 | 8/24/2020 | 8/17/2020 |
Customer5 | 1 | 8/31/2020 | 8/17/2020 |
Customer5 | 2 | 9/7/2020 | 8/17/2020 |
Customer5 | 1 | 9/14/2020 | 8/17/2020 |
Customer5 | 3 | 9/14/2020 | 8/17/2020 |
Customer5 | 1 | 9/21/2020 | 8/17/2020 |
Customer5 | 2 | 9/21/2020 | 8/17/2020 |
Customer5 | 1 | 9/21/2020 | 8/17/2020 |
Customer5 | 1 | 9/28/2020 | 8/17/2020 |
Customer5 | 1 | 9/28/2020 | 8/17/2020 |
Customer5 | 5 | 10/5/2020 | 8/17/2020 |
Customer5 | 1 | 10/12/2020 | 8/17/2020 |
Customer6 | 2 | 8/3/2020 | 8/3/2020 |
Customer6 | 1 | 8/10/2020 | 8/3/2020 |
Customer6 | 1 | 8/10/2020 | 8/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!
Solved! Go to Solution.
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
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:
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?
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.