Calculating net requirements: spread available stock until depletion

[Anonymous]

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;