Assign dates in FIFO logic for a certain number of rows

[PhuongTran52]

Hi I have an inventory information like this:

1. Stock in table: will have Stock In date - Model - Product ID

- where the product ID is unique to each product under the same SKU

2. Stock out table: will have Sales date - Model - Number of product sold, so let’s say on day 1 I can sold 5 units of product A

It’s a first in first out problem, I want to assign the sales out date to each of my Product ID. For example, if in day 1, I have stocked in 5 items = 5 ID of model A, and O’ve sold 3 model A items, then 3/5 Product ID of model A will be assigned the sales date as day 1. If next day I only sell 1 product then the 4th item stocked in day 1 will have sales date of day 2

If I sell no item then no Product ID get assigned any sales date

please help me thank you so much

[KaneG]

I see that you found your answer, for anyone else that is looking and finds this question, take a look at one of the following posts:

• https://community.alteryx.com/t5/Alteryx-Designer-Discussions/Stock-Ageing-Basis-FIFO-method/td-p/55...
• https://community.alteryx.com/t5/Alteryx-Designer-Discussions/StockAge-FIFO/td-p/643004

[PhuongTran52]

Hi @KaneG 

I found the 2 solutions really smart. However, I am a bit struggling when applying any of these 2 due to

• For the (1) solution: my data is too big for iterative macro (it has like 8k of sales record with 2 mill items). I am running a similar workflow applying the 1st one but's it's been running for 10 hours... I am also struggle to estimate the right number of maximum iterative allowed
• For the (2) solution: some sales records needed to be traced back to more than 2-3 stock in records to be able to be fulfilled

Wondering anyone can help 😞 

[KaneG]

Do you have a sample of what you have so far?

[PhuongTran52]

Hi @KaneG 

Thank you for asking, I have my data as below, this is a cut of one SKU with low volume

I have my Sales out data, Stock in data, and detailed Stock In with Unique ID, and my desired output. I do this manually, but with more SKU and order quantities I am really struggle

Thank you for asking again...

[KaneG]

That data is identical to the second link that I posted above, where I also posted a solution in order to look back. I'm at a loss to see what's different. Can you please be more specific as to what needs to change?