Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
Here is my original data set in the application.
REQ field is just repeating.
Any help ism highly appreciated.
ITEM | Location | Available | AvailableDate | REQ | Can fill |
1 | Dallas | 204 | 10/24/2017 | 104 | 104 |
1 | Dallas | 300 | 11/5/2017 | 104 | |
1 | Dallas | 60 | 11/15/2017 | 104 | |
1 | Dallas | 204 | 12/1/2017 | 104 | |
1 | Dallas | 204 | 12/2/2017 | 104 | |
2 | Chicago | 35 | 10/21/2017 | 2 | 2 |
2 | Chicago | 40 | 12/1/2017 | 2 | |
2 | Chicago | 44 | 12/1/2017 | 2 | |
2 | NY | 38 | 12/5/2017 | 3 | |
2 | NY | 43 | 12/5/2017 | 3 | |
3 | Chicago | 300 | 10/21/2017 | 250 | 250 |
3 | Chicago | 0 | 12/1/2017 | 2 | |
3 | Chicago | 0 | 12/1/2017 | 2 | |
3 | NY | 3 | 12/5/2017 | 400 | 3 |
3 | NY | 10 | 12/5/2017 | 3 |
Can you please give a look?
Mark,
Try this
ITEMTEMP:
LOAD
ITEM,
Location,
Available,
AvailableDate,
REQ
FROM [lib://DataDiscoverySharedFolder/New folder\sample.xlsx]
(ooxml, embedded labels, table is Sheet2)
;
NoConcatenate
ITEM:
LOAD
ITEM,
Location,
Available,
AvailableDate,
REQ,
if(isnull(CANFILL),0,CANFILL)+ if(IsNull(Pavail),0, if(ITEM&Location= previtemdate,Pavail,0)) as CANFILL ,
BALANCE ;
Load *,
IF(REQ>Available,Available,REQ) AS CANFILL,
IF(REQ>=Available,REQ-Available) AS BALANCE,
PEEK('ITEM')&peek('Location') as previtemdate,
peek('BALANCE') as Pavail
Resident ITEMTEMP ORDER BY ITEM, Location,AvailableDate ;
DROP TABLE ITEMTEMP;