ALTERYX INSPIRE | Join us this May for for a multi-day virtual analytics + data science experience like no other! Register Now
The Alteryx Community will be temporarily unavailable for a few hours due to scheduled maintenance starting on Thursday, April 22nd at 5pm MST. Please plan accordingly.

Alteryx Designer Discussions

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

Join function using a date : take the previous date if the current date is missing

litane
5 - Atom

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

 

DateProductTypeWarehouseQuantity in KGM
31/01/2020 00:00RM0001RMN32400,00
29/02/2020 00:00RM0001RMN32450,00
31/03/2020 00:00RM0001RMN32300,00
30/04/2020 00:00RM0001RMN32150,00
31/05/2020 00:00RM0001RMN32220,00
30/06/2020 00:00RM0001RMN32600,00
31/07/2020 00:00RM0001RMN32200,00
31/08/2020 00:00RM0001RMN32400,00
30/09/2020 00:00RM0001RMN32450,00
31/10/2020 00:00RM0001RMN32300,00
30/11/2020 00:00RM0001RMN32150,00
31/12/2020 00:00RM0001RMN32220,00
31/01/2020 00:00RM0001RMH93600,00
29/02/2020 00:00RM0001RMH93200,00
31/03/2020 00:00RM0001RMH93400,00
30/04/2020 00:00RM0001RMH93450,00
31/05/2020 00:00RM0001RMH93300,00
30/06/2020 00:00RM0001RMH93150,00
31/07/2020 00:00RM0001RMH93220,00
31/08/2020 00:00RM0001RMH93600,00
30/09/2020 00:00RM0001RMH93200,00
31/10/2020 00:00RM0001RMH93400,00
30/11/2020 00:00RM0001RMH93450,00
31/12/2020 00:00RM0001RMH93300,00
31/01/2020 00:00FG0001FGN32220,00
29/02/2020 00:00FG0001FGN32600,00
31/03/2020 00:00FG0001FGN32200,00
30/04/2020 00:00FG0001FGN32126,00
31/05/2020 00:00FG0001FGN32400,00
30/06/2020 00:00FG0001FGN3230,00
31/07/2020 00:00FG0001FGN32698,00
31/08/2020 00:00FG0001FGN32500,00
30/09/2020 00:00FG0001FGN32124,00
31/10/2020 00:00FG0001FGN32413,71
30/11/2020 00:00FG0001FGN32442,93
31/12/2020 00:00FG0001FGN32472,14
31/01/2020 00:00FG0001FGH93501,36
29/02/2020 00:00FG0001FGH93530,57
31/03/2020 00:00FG0001FGH93559,79
30/04/2020 00:00FG0001FGH93589,00
31/05/2020 00:00FG0001FGH93618,21
30/06/2020 00:00FG0001FGH93647,43
31/07/2020 00:00FG0001FGH93676,64
31/08/2020 00:00FG0001FGH93705,86
30/09/2020 00:00FG0001FGH93735,07
31/10/2020 00:00FG0001FGH93764,29
30/11/2020 00:00FG0001FGH93793,50
31/12/2020 00:00FG0001FGH93822,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    

 

DateProductTypeWarehouseCost price in USD
31/01/2020 00:00RM0001RMN32400,00
29/02/2020 00:00RM0001RMN32450,00
31/03/2020 00:00RM0001RMN32300,00
30/04/2020 00:00RM0001RMN32150,00
31/08/2020 00:00RM0001RMN32400,00
30/09/2020 00:00RM0001RMN32450,00
31/10/2020 00:00RM0001RMN32300,00
30/11/2020 00:00RM0001RMN32150,00
31/12/2020 00:00RM0001RMN32220,00
31/01/2020 00:00RM0001RMH93600,00
29/02/2020 00:00RM0001RMH93200,00
30/06/2020 00:00RM0001RMH93150,00
31/07/2020 00:00RM0001RMH93220,00
31/08/2020 00:00RM0001RMH93600,00
30/09/2020 00:00RM0001RMH93200,00
31/10/2020 00:00RM0001RMH93400,00
30/11/2020 00:00RM0001RMH93450,00
31/12/2020 00:00RM0001RMH93300,00
31/01/2020 00:00FG0001FGN32220,00
29/02/2020 00:00FG0001FGN32600,00
31/03/2020 00:00FG0001FGN32200,00
30/04/2020 00:00FG0001FGN32126,00
31/05/2020 00:00FG0001FGN32400,00
30/06/2020 00:00FG0001FGN3230,00
30/09/2020 00:00FG0001FGN32124,00
31/10/2020 00:00FG0001FGN32413,71
30/11/2020 00:00FG0001FGN32442,93
31/12/2020 00:00FG0001FGN32472,14
31/01/2020 00:00FG0001FGH93501,36
29/02/2020 00:00FG0001FGH93530,57
31/03/2020 00:00FG0001FGH93559,79
30/04/2020 00:00FG0001FGH93589,00
30/09/2020 00:00FG0001FGH93735,07
31/10/2020 00:00FG0001FGH93764,29
30/11/2020 00:00FG0001FGH93793,50
31/12/2020 00:00FG0001FGH93822,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  

AngelosPachis
15 - Aurora

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.

 

AngelosPachis_0-1614337620478.png

 

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

Qiu
17 - Castor

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

0226-litane.PNG

Qiu
17 - Castor

@AngelosPachis 
nice one. Now mine is looking ugly😁

litane
5 - Atom

Thank you so much guys, that was easier than what i thought 

I used a multi row formula and it worked perfectly !! 

Cheers 

AngelosPachis
15 - Aurora

@Qiu  It might be a bit more busy but it gets the job done 🙂

sreenivasateja
8 - Asteroid

Hi @litane 

 

please find my solution for your query..

sreenivasateja_0-1614340343085.png

 

Best,

Teja.

Labels