Here's one way that seems to work:
Date(Date#(DATE,'DD-MMM-YY')) as DATE,
if(AMOUNT>0,1) as POS_FLAG;
LOAD * INLINE [
CLIENT_KEY, DATE, AMOUNT
R1015, 05-Sep-12, "-18192"
R1015, 06-Sep-12, "2889"
R1015, 10-Sep-12, "5819"
R1720, 18-Sep-12, "6450"
R1720, 28-Sep-12, -70
R1720, 01-Oct-12, -23
R1720, 04-Oct-12, 1678
if(Peek(CLIENT_KEY)<>CLIENT_KEY,AMOUNT,if(Peek(NEW_AMOUNT)>0 and POS_FLAG=1,AMOUNT)) as NEW_AMOUNT
order by CLIENT_KEY, DATE desc;
Drop Table T1;
Thanks Johannes Sunden,
This works. I tried it with different data sets.
I was hoping to do it this after the load, because the AMOUNT values in the DB are chronologically accumulated after they are loaded,
Im assuming these values could be cumulated in the load. How would i chronologically accumulate AMOUNT values for every client in the script ?