Peek and allocate units

[markgraham123]

Hi all,

I'm working on a requirement to allocate units based on my available stock:

Order	item	Req	Able to fill
1	A	10	10
2	A	20	5
3	B	15	10
4	C	20	20

item	Available
A	15
B	10
C	20

Any help is highly appreciated!

[vvira1316]

Hi Mark,

See if following will help.

Order	item	Req
1	A	10
2	A	20
3	B	5
4	C	20
5	C	5
6	B	15
7	D	10

item	Available
A	15
B	10
C	20

NoConcatenate

OrderTableTmp:

LOAD Order,

     item,

     Req

FROM

Fill.xlsx

(ooxml, embedded labels, table is Order);

NoConcatenate

AvailableTableTmp:

LOAD item,

     Available

FROM

Fill.xlsx

(ooxml, embedded labels, table is Available);

NoConcatenate

OrderCountTableTmp:

LOAD item,

     Count(DISTINCT Order) as OrderCount

Resident OrderTableTmp

Group by item

Order By item, Order;

LET vNoOfRowsAvailable = NoOfRows('AvailableTableTmp');

LET vNoOfRowsOrderCount = NoOfRows('OrderCountTableTmp');

IF $(vNoOfRowsAvailable) > $(vNoOfRowsOrderCount) then

NoConcatenate

AvailableTable:

LOAD item,

     Available

Resident AvailableTableTmp;

Left Join

LOAD item,

     OrderCount

Resident OrderCountTableTmp;

ELSEIF $(vNoOfRowsOrderCount) >= $(vNoOfRowsAvailable) then

NoConcatenate

AvailableTable:

LOAD item,

     OrderCount

Resident OrderCountTableTmp;

Left Join

LOAD item,

     Available

Resident AvailableTableTmp;

ENDIF;

DROP Table AvailableTableTmp;

DROP Table OrderCountTableTmp;

IF $(vNoOfRowsAvailable) > $(vNoOfRowsOrderCount) then

NoConcatenate

OrderTable:

LOAD item,

Available,

OrderCount

Resident AvailableTableTmp;

Left Join

LOAD item,

     Order,

     Req

Resident OrderTableTmp;

ELSEIF $(vNoOfRowsOrderCount) >= $(vNoOfRowsAvailable) then

NoConcatenate

OrderTable:

LOAD item,

     Order,

     Req

Resident OrderTableTmp;

Left Join

LOAD item,

Available,

OrderCount

Resident AvailableTable;

ENDIF;

DROP Table OrderTableTmp;

DROP Table AvailableTable;

LET vNoOfRows = NoOfRows('OrderTable');

LET vStoreItem = 0; // Null();

LET vAbelToFill = 0;

FOR i = 0 to $(vNoOfRows) - 1

LET vItem = Peek('item', $(i), 'OrderTable');

LET vOrder = Peek('Order', $(i), 'OrderTable');

LET vReq = Peek('Req', $(i), 'OrderTable');

LET vAvailable = Peek('Available', $(i), 'OrderTable');

LET vOrderCount = Peek('OrderCount', $(i), 'OrderTable');

IF vItem <> vStoreItem then

LET vStoreItem = vItem;

LET vAbelToFill = 0;

LET vStoreAvailable = vAvailable;

ENDIF;

IF vReq <= vStoreAvailable then

LET vAbelToFill = vReq;

LET vStoreAvailable = vStoreAvailable - vAbelToFill;

ELSEIF vReq > vStoreAvailable then

LET vAbelToFill = vStoreAvailable;

LET vStoreAvailable = vStoreAvailable - vAbelToFill;

ENDIF;

ProcessTable:

LOAD *

Inline [

item, Order, Req, AbelToFill

"$(vItem)", "$(vOrder)", "$(vReq)", "$(vAbelToFill)"

];

NEXT i;

DROP Table OrderTable;

[techvarun]

Try the below script

T1:

LOAD * INLINE  [

item, Available

    A, 15

    B, 10

    C, 20

];

join

LOAD * INLINE [

    Order, item, Req

    1, A, 10

    2, A, 20

    3, B, 15

    4, C, 20

];

T2:

Load *, if(Previous(item) = item, Available- previous(Req), Available) as InQTY

Resident T1 order by item, Req;

Drop table T1;

T3:

load *,

if(InQTY >Req, Req, InQTY) as [Able to Fill] Resident T2;

Drop table T2;