Divide Items based on their available stock to other ID

Question

Hi Everyone i am facing a unique problem . I want to find out Transfer of Inventory based on condition if Quantity required for a particular Item id is more than Stock on hand. we should transfer the inventory from other ID. For Example. item I60 is available for 7 IDs. For E1, E6 available stock is less than quantity so what i want to do is Transfer the excess inventory from E3 (i.e 6-2 =4) to E1 and E6. So transfer for E1 will be 1 and E6 will be 2 and remaining SOH from E3 will be 3. I hope everyone can understand it.

structure(list(ID = structure(c(1L, 6L, 7L, 3L, 5L, 2L, 4L, 8L, 
1L, 7L, 3L, 5L, 2L, 9L, 8L, 1L, 7L, 3L, 5L, 2L, 9L, 8L, 1L, 7L, 
3L, 5L, 2L, 9L, 8L, 1L, 7L, 3L, 5L, 2L, 9L, 8L, 1L, 7L, 3L, 5L, 
2L, 9L, 8L, 1L, 7L, 3L, 5L, 2L, 9L), .Label = c("E1", "E2", "E3", 
"E4", "E5", "E6", "E7", "E8", "E9"), class = "factor"), Item.Code = structure(c(1L, 
1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 
3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 5L, 5L, 5L, 5L, 5L, 
5L, 5L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 7L, 7L, 7L, 7L, 7L, 7L, 7L
), .Label = c("I60", "I67", "I68", "I69", "I70", "I71", "I72"
), class = "factor"), Quantity = c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 4L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), Stock_on_hand = c(1L, 
0L, 2L, 6L, 2L, 2L, 2L, 0L, 6L, 3L, -1L, 1L, 2L, 9L, 1L, 5L, 
-1L, 9L, 3L, 38L, 5L, 10L, 2L, 3L, 2L, 2L, 1L, 8L, 0L, 2L, 2L, 
4L, 2L, 1L, 5L, 1L, -1L, 4L, 3L, 1L, 2L, 11L, 1L, 2L, 0L, 3L, 
1L, 4L, 1L), Transfer = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 4L, 0L, 
0L, 0L, 0L, 0L, 0L, 3L, 0L, 7L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
0L, 6L, 0L, 0L, 0L, 0L, 0L, 0L, 3L, 0L, 0L, 2L, 0L, 0L, 0L, 0L, 
0L, 0L, 0L, 1L, 0L, 2L, 0L, 0L)), class = "data.frame", row.names = c(NA, 
-49L))

highlighted columns should be generated with R code

I've got no idea what you're trying to do (I struggle to understand your verbose exposition). Can you please include your expected output for your minimal sample data; also clearly state (in as few a words and as succinctly as possible) what the rules are for creating the new column. Avoid domain-specific jargon and instead focus on the general rules. — Maurits Evers, Sep 04 '18 at 12:09
@MauritsEvers i have done changes in the question it self. i hope you can now understand it clear. — Hunaidkhan, Sep 04 '18 at 13:39
Okay, I think I get it. What do you want to do if the total stock is less than the total quantity for an item? — Gregor Thomas, Sep 04 '18 at 13:43
@Gregor if it is less than the quantity it should be transferred from a other ID. If no ID has excess stock it should stay as it is. — Hunaidkhan, Sep 04 '18 at 13:44
What have you tried so far? It's helpful to see what hasn't worked, and makes the process more clear — camille, Sep 04 '18 at 14:21

score 1 · Answer 1 · answered Sep 05 '18 at 01:06

Looks like an integer programming problem with Updated_SOH as the variable and objective function minimizing absolute difference between Quantity and SOH subject to constraint that total sum of SOH stays constant.

Here is a heuristic approach to solve this optimization problem:

1) Calculate the difference which is to be used to sort the dataset.

2) In a similar approach as here but shifting positive values and different aggregation, we use these excess SOH to net off SOH deficit in previous rows.

3) The final output is the sum of i) existing Quantity, ii) any unfulfilled Quantity and iii) excess SOH.

setDT(df)    
df[, Diff := Stock_on_hand - Quantity]
setorder(df, Item.Code, Diff)

df[, Updated_SOH := {
    posVal <- replace(Diff, Diff<0, 0)
    negVal <- replace(Diff, Diff>0, 0)
    n <- 1L
    while (any(negVal < 0) && n < .N) {
        negVal <- replace(negVal, negVal>0, 0) + 
            shift(posVal, 1L, type="lead", fill=0) +
            c(posVal[1L], rep(0, .N-1L)) #for case where there are more Quantity than SOH
        posVal <- replace(negVal, negVal<0, 0)
        n <- n + 1L
    }

    excess <- negVal[negVal > 0]

    Quantity +                                           #existing Quantity
        replace(negVal, negVal>0, 0) +                   #unfulfilled Quantity
        c(rep(0, .N - length(excess)), excess) #shifting back down excess SOH
}, by=.(Item.Code)]

output:

    ID Item.Code Quantity Stock_on_hand Transfer Diff Updated_SOH
 1: E6       I60        2             0        0   -2           2
 2: E1       I60        2             1        0   -1           2
 3: E7       I60        2             2        0    0           2
 4: E5       I60        2             2        0    0           2
 5: E2       I60        2             2        0    0           2
 6: E4       I60        2             2        0    0           2
 7: E3       I60        2             6        0    4           3
 8: E3       I67        2            -1        0   -3           2
 9: E8       I67        2             0        4   -2           2
10: E5       I67        2             1        0   -1           2
11: E2       I67        2             2        0    0           2
12: E7       I67        2             3        0    1           2
13: E1       I67        2             6        0    4           2
14: E9       I67        2             9        0    7           8
15: E7       I68        2            -1        7   -3           2
16: E8       I68        2             1        3   -1           2
17: E5       I68        2             3        0    1           2
18: E1       I68        2             5        0    3           2
19: E9       I68        2             5        0    3           5
20: E3       I68        2             9        0    7           9
21: E2       I68        4            38        0   34          38
22: E2       I69        2             1        6   -1           2
23: E1       I69        2             2        0    0           2
24: E3       I69        2             2        0    0           2
25: E5       I69        2             2        0    0           2
26: E7       I69        2             3        0    1           2
27: E9       I69        2             8        0    6           8
28: E8       I69        2            10        0    8          10
29: E8       I70        2             0        0   -2           2
30: E2       I70        2             1        3   -1           2
31: E1       I70        2             2        0    0           2
32: E7       I70        2             2        0    0           2
33: E5       I70        2             2        0    0           2
34: E3       I70        2             4        0    2           2
35: E9       I70        2             5        0    3           4
36: E1       I71        2            -1        2   -3           2
37: E8       I71        2             1        0   -1           2
38: E5       I71        2             1        0   -1           2
39: E2       I71        2             2        0    0           2
40: E3       I71        2             3        0    1           2
41: E7       I71        2             4        0    2           2
42: E9       I71        2            11        0    9           9
43: E7       I72        2             0        1   -2           0
44: E8       I72        2             1        0   -1           2
45: E5       I72        2             1        2   -1           2
46: E9       I72        2             1        0   -1           2
47: E1       I72        2             2        0    0           2
48: E3       I72        2             3        0    1           2
49: E2       I72        2             4        0    2           2
    ID Item.Code Quantity Stock_on_hand Transfer Diff Updated_SOH

data:

library(data.table)
df <- structure(list(ID = structure(c(1L, 6L, 7L, 3L, 5L, 2L, 4L, 8L, 
    1L, 7L, 3L, 5L, 2L, 9L, 8L, 1L, 7L, 3L, 5L, 2L, 9L, 8L, 1L, 7L, 
    3L, 5L, 2L, 9L, 8L, 1L, 7L, 3L, 5L, 2L, 9L, 8L, 1L, 7L, 3L, 5L, 
    2L, 9L, 8L, 1L, 7L, 3L, 5L, 2L, 9L), .Label = c("E1", "E2", "E3", 
        "E4", "E5", "E6", "E7", "E8", "E9"), class = "factor"), Item.Code = structure(c(1L, 
            1L, 1L, 1L, 1L, 1L, 1L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 3L, 3L, 3L, 
            3L, 3L, 3L, 3L, 4L, 4L, 4L, 4L, 4L, 4L, 4L, 5L, 5L, 5L, 5L, 5L, 
            5L, 5L, 6L, 6L, 6L, 6L, 6L, 6L, 6L, 7L, 7L, 7L, 7L, 7L, 7L, 7L
        ), .Label = c("I60", "I67", "I68", "I69", "I70", "I71", "I72"
        ), class = "factor"), Quantity = c(2L, 2L, 2L, 2L, 2L, 2L, 2L, 
            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 4L, 2L, 2L, 2L, 
            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 
            2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L, 2L), Stock_on_hand = c(1L, 
                0L, 2L, 6L, 2L, 2L, 2L, 0L, 6L, 3L, -1L, 1L, 2L, 9L, 1L, 5L, 
                -1L, 9L, 3L, 38L, 5L, 10L, 2L, 3L, 2L, 2L, 1L, 8L, 0L, 2L, 2L, 
                4L, 2L, 1L, 5L, 1L, -1L, 4L, 3L, 1L, 2L, 11L, 1L, 2L, 0L, 3L, 
                1L, 4L, 1L), Transfer = c(0L, 0L, 0L, 0L, 0L, 0L, 0L, 4L, 0L, 
                    0L, 0L, 0L, 0L, 0L, 3L, 0L, 7L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 0L, 
                    0L, 6L, 0L, 0L, 0L, 0L, 0L, 0L, 3L, 0L, 0L, 2L, 0L, 0L, 0L, 0L, 
                    0L, 0L, 0L, 1L, 0L, 2L, 0L, 0L)), class = "data.frame", row.names = c(NA, 
                        -49L))

For example in Row number 21 :- Stock on hand is 38 and quantity is 4 so. updated SOH should be 34 but what i am getting in the above solution is 4 in the field of SOH. Instead of 4 it should be 34. please help me out. — Hunaidkhan, Sep 25 '18 at 12:21
The problem is happening when many items from single Item.code are having more Stock on hand than quantity. All the items in the output shows Quantity as updated SOH instead of actual SOH because there will not be any transfer as all the items has sufficient Stock. I hope you understand — Hunaidkhan, Sep 25 '18 at 13:02
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/180734/discussion-between-hunaidkhan-and-chinsoon12). — Hunaidkhan, Sep 25 '18 at 13:09
your new dataset has 2 Stock_on_hand and SOH columns. what are you trying to solve? maybe u can ask a new question. — chinsoon12, Sep 26 '18 at 01:19

Divide Items based on their available stock to other ID

1 Answers1