Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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;
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;