I have a list of stores with some quantities of different articles in them and a warehouse with these articles - these are two separate data frames.
Article <- c('a','b','a','b','c','d')
forecast <- c( 1,5,80,10,100,1000)
StoreID <- c(1,1,2,2,3,4)
StoreData <- data.frame(StoreID, Article, Order)
Smth like this:
StoreData
StoreID Article forecast
1 a 1
1 b 5
2 a 80
2 b 10
3 c 100
4 d 1000
And the warehouse data:
Stock <- c(10,11,12,100)
WarehouseData <- data.frame(Article, Stock)
WarehouseData
Article Stock
a 10
b 11
c 12
d 100
My target is to have a purchase order column. The logic has to be following: for every row in the StoreData table I have to look at the Stock of the Article in the Warehouse and if it is enough - approve the fcst, if not - approve only the avaulable quantity. My problem is that while approving quantities the avaialble stock is reducing and I cannot find out how to take it into account.
The expected result looks like this:
StoreData
StoreID Article forecast PO
1 a 1 1
1 b 5 5
2 a 80 9
2 b 10 6
3 c 100 12
4 d 1000 100
Can anyone, please, tell how to get it right?