Dear community
I'm working on business case regarding inventories, and my client wants to be able to track inventories' evolution overtime in quantity and value
In order to simplify my issue, let's assume that i have two tables
Table 1 : it shows my stock in quantity at the end of each month for each product and Warehouse, this information must be available each month
Date | Product | Type | Warehouse | Quantity in KGM |
31/01/2020 00:00 | RM0001 | RM | N32 | 400,00 |
29/02/2020 00:00 | RM0001 | RM | N32 | 450,00 |
31/03/2020 00:00 | RM0001 | RM | N32 | 300,00 |
30/04/2020 00:00 | RM0001 | RM | N32 | 150,00 |
31/05/2020 00:00 | RM0001 | RM | N32 | 220,00 |
30/06/2020 00:00 | RM0001 | RM | N32 | 600,00 |
31/07/2020 00:00 | RM0001 | RM | N32 | 200,00 |
31/08/2020 00:00 | RM0001 | RM | N32 | 400,00 |
30/09/2020 00:00 | RM0001 | RM | N32 | 450,00 |
31/10/2020 00:00 | RM0001 | RM | N32 | 300,00 |
30/11/2020 00:00 | RM0001 | RM | N32 | 150,00 |
31/12/2020 00:00 | RM0001 | RM | N32 | 220,00 |
31/01/2020 00:00 | RM0001 | RM | H93 | 600,00 |
29/02/2020 00:00 | RM0001 | RM | H93 | 200,00 |
31/03/2020 00:00 | RM0001 | RM | H93 | 400,00 |
30/04/2020 00:00 | RM0001 | RM | H93 | 450,00 |
31/05/2020 00:00 | RM0001 | RM | H93 | 300,00 |
30/06/2020 00:00 | RM0001 | RM | H93 | 150,00 |
31/07/2020 00:00 | RM0001 | RM | H93 | 220,00 |
31/08/2020 00:00 | RM0001 | RM | H93 | 600,00 |
30/09/2020 00:00 | RM0001 | RM | H93 | 200,00 |
31/10/2020 00:00 | RM0001 | RM | H93 | 400,00 |
30/11/2020 00:00 | RM0001 | RM | H93 | 450,00 |
31/12/2020 00:00 | RM0001 | RM | H93 | 300,00 |
31/01/2020 00:00 | FG0001 | FG | N32 | 220,00 |
29/02/2020 00:00 | FG0001 | FG | N32 | 600,00 |
31/03/2020 00:00 | FG0001 | FG | N32 | 200,00 |
30/04/2020 00:00 | FG0001 | FG | N32 | 126,00 |
31/05/2020 00:00 | FG0001 | FG | N32 | 400,00 |
30/06/2020 00:00 | FG0001 | FG | N32 | 30,00 |
31/07/2020 00:00 | FG0001 | FG | N32 | 698,00 |
31/08/2020 00:00 | FG0001 | FG | N32 | 500,00 |
30/09/2020 00:00 | FG0001 | FG | N32 | 124,00 |
31/10/2020 00:00 | FG0001 | FG | N32 | 413,71 |
30/11/2020 00:00 | FG0001 | FG | N32 | 442,93 |
31/12/2020 00:00 | FG0001 | FG | N32 | 472,14 |
31/01/2020 00:00 | FG0001 | FG | H93 | 501,36 |
29/02/2020 00:00 | FG0001 | FG | H93 | 530,57 |
31/03/2020 00:00 | FG0001 | FG | H93 | 559,79 |
30/04/2020 00:00 | FG0001 | FG | H93 | 589,00 |
31/05/2020 00:00 | FG0001 | FG | H93 | 618,21 |
30/06/2020 00:00 | FG0001 | FG | H93 | 647,43 |
31/07/2020 00:00 | FG0001 | FG | H93 | 676,64 |
31/08/2020 00:00 | FG0001 | FG | H93 | 705,86 |
30/09/2020 00:00 | FG0001 | FG | H93 | 735,07 |
31/10/2020 00:00 | FG0001 | FG | H93 | 764,29 |
30/11/2020 00:00 | FG0001 | FG | H93 | 793,50 |
31/12/2020 00:00 | FG0001 | FG | H93 | 822,71 |
Table 2 : it shows the cost price for each product and Warehouse, but it is not available each month as you can see below, sometimes the cost doesn't really change and the production doesn't run the calculation because it's time consuming, they only run it when there are relevant changes
Date | Product | Type | Warehouse | Cost price in USD |
31/01/2020 00:00 | RM0001 | RM | N32 | 400,00 |
29/02/2020 00:00 | RM0001 | RM | N32 | 450,00 |
31/03/2020 00:00 | RM0001 | RM | N32 | 300,00 |
30/04/2020 00:00 | RM0001 | RM | N32 | 150,00 |
31/08/2020 00:00 | RM0001 | RM | N32 | 400,00 |
30/09/2020 00:00 | RM0001 | RM | N32 | 450,00 |
31/10/2020 00:00 | RM0001 | RM | N32 | 300,00 |
30/11/2020 00:00 | RM0001 | RM | N32 | 150,00 |
31/12/2020 00:00 | RM0001 | RM | N32 | 220,00 |
31/01/2020 00:00 | RM0001 | RM | H93 | 600,00 |
29/02/2020 00:00 | RM0001 | RM | H93 | 200,00 |
30/06/2020 00:00 | RM0001 | RM | H93 | 150,00 |
31/07/2020 00:00 | RM0001 | RM | H93 | 220,00 |
31/08/2020 00:00 | RM0001 | RM | H93 | 600,00 |
30/09/2020 00:00 | RM0001 | RM | H93 | 200,00 |
31/10/2020 00:00 | RM0001 | RM | H93 | 400,00 |
30/11/2020 00:00 | RM0001 | RM | H93 | 450,00 |
31/12/2020 00:00 | RM0001 | RM | H93 | 300,00 |
31/01/2020 00:00 | FG0001 | FG | N32 | 220,00 |
29/02/2020 00:00 | FG0001 | FG | N32 | 600,00 |
31/03/2020 00:00 | FG0001 | FG | N32 | 200,00 |
30/04/2020 00:00 | FG0001 | FG | N32 | 126,00 |
31/05/2020 00:00 | FG0001 | FG | N32 | 400,00 |
30/06/2020 00:00 | FG0001 | FG | N32 | 30,00 |
30/09/2020 00:00 | FG0001 | FG | N32 | 124,00 |
31/10/2020 00:00 | FG0001 | FG | N32 | 413,71 |
30/11/2020 00:00 | FG0001 | FG | N32 | 442,93 |
31/12/2020 00:00 | FG0001 | FG | N32 | 472,14 |
31/01/2020 00:00 | FG0001 | FG | H93 | 501,36 |
29/02/2020 00:00 | FG0001 | FG | H93 | 530,57 |
31/03/2020 00:00 | FG0001 | FG | H93 | 559,79 |
30/04/2020 00:00 | FG0001 | FG | H93 | 589,00 |
30/09/2020 00:00 | FG0001 | FG | H93 | 735,07 |
31/10/2020 00:00 | FG0001 | FG | H93 | 764,29 |
30/11/2020 00:00 | FG0001 | FG | H93 | 793,50 |
31/12/2020 00:00 | FG0001 | FG | H93 | 822,71 |
What i'm trying to do, is linking the quantities of my stock with their related cost price, for this i use a join function with the date, the product, the product type and warehouse, however, for the months that are missing, the cost price is blank
What i would like to do, is take the latest available value when the month is missing, for example, if the cost price of May 2020 is missing, i take April's cost price, and if this is also missing, i take March's and so one
I believe this could be done easily with Alteryx, maybe through an iterative macro, which is something that i'm still not familiar with
If you have any idea how so solve this, please let me know and that would be very appreciated
Many thanks
Cheers
Solved! Go to Solution.
Hi @litane ,
You can achieve that with a multi-row formula. What this tool will allow you to do once your data is sorted per warehouse, product and date is if you don't have the information for cost for one cell, you can take the value from the cell above.
This can be done with an If statement, that checks if the current cell is null or not. If it is, then grab the value from above, if it's not then leave the value there.
Hope that helps,
Cheers,
Angelos
@litane
I am sure an interative macro can do it. But I gave a try without macro.
Try to take minimum difference between previous months.
@AngelosPachis
nice one. Now mine is looking ugly😁
Thank you so much guys, that was easier than what i thought
I used a multi row formula and it worked perfectly !!
Cheers
@Qiu It might be a bit more busy but it gets the job done 🙂