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.

Assign dates in FIFO logic for a certain number of rows

PhuongTran52
7 - Meteor

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

 

5 REPLIES 5
KaneG
Alteryx Alumni (Retired)

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:

 

PhuongTran52
7 - Meteor

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
Alteryx Alumni (Retired)

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

PhuongTran52
7 - Meteor

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
Alteryx Alumni (Retired)

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?

Labels