Peek and create flag

[markgraham123]

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

[markgraham123]

stalwar1‌

Can you please give a look?

[sujith1011]

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;