I have two data files. One contains orders (day and article identifier) and the other one contains the returns (day and article identifier). I want to calculate the inventory level (inventory level of day before - orders + returns). Furthermore, not every day there is an order and/or a return.
I think it is very simple but I tried many things (Java Snippet, Python, "normal nodes") and didn`t get it. Can give me someone an advice how I could do this?
Thanks a lot
I would need some sample data to fully understand the problem. But this is what I would do
1. First create a list of dates for which you need to now the inventory level Could be a full list
2. append a column with the value +1 for the returns and -1 for the orders.
3. Create one table for returns and orders
4. join this table to the list of dates
5. create a "first row" containing the original amounts of inventory
5b) sort by the date column
6. Use the moving aggregation node and calculate a cumulative sum
This cum sum will be your inventory.
Let me know if this solved your problem.
Best regards, Iris