Calculating net requirements: spread available stock until depletion

    Hi,

     

    I just like to contribute a little demo that is quiet easy to understand. It could be used for calculating production demands for example.

    In other words: as long as there's available stock, nothing needs to be produced or ordered. I didn't find any similar solution within the community, so I just post it...

     

    If you load one preceeding load after the other you'll understand how it works. The basic trick is to fill the field "AVAILABLE_STOCK_DEPLETED" with the current partnumber. So you're able to recognize that the stock of a certain partnumber ist depleted...

     

     

    TEMPTABLE:
    LOAD RowNo() as ##,* INLINE [
    PARTNUMBER, AVAILABLE_STOCK, DEMAND
    A, 10, 5
    A, 10, 3
    A, 10, 7
    A, 10, 2
    B, 20, 30
    B, 20, 10
    B, 20, 100
    F, 10, 10
    C, 30, 2
    C, 30, 3
    C, 30, 4
    D, 40, 5
    D, 40, 50
    D, 40, 3
    E, 50, 100
    I, 20, 10
    G, 40, 55

    ]
    ;

    NoConcatenate

    FINAL:
    LOAD
    *,
    if(REST>=0,0,
    if(DELTA_FLAG='X',-REST,DEMAND)
    )
    as PRODUCTION_DEMAND
    ;
    LOAD
    *,
    if(not isnull(AVAILABLE_STOCK_DEPLETED) and AVAILABLE_STOCK_DEPLETED<>Previous(AVAILABLE_STOCK_DEPLETED),'X') as DELTA_FLAG
    ;

    LOAD
    if(REST<0,PARTNUMBER) as AVAILABLE_STOCK_DEPLETED,
    *
    ;
    LOAD
    ##,
    PARTNUMBER,
    AVAILABLE_STOCK,
    DEMAND,
    if(PARTNUMBER<>Previous(PARTNUMBER),
    RANGESUM(AVAILABLE_STOCK,-DEMAND),
    RANGESUM(AVAILABLE_STOCK,-DEMAND,-Peek('TEMP'))
    )
    as REST,

    if(PARTNUMBER<>Previous(PARTNUMBER),
    DEMAND,
    Rangesum(DEMAND,Peek('TEMP'))

    )
    as TEMP
    Resident TEMPTABLE;

    DROP Table TEMPTABLE;