Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi
I have the closing balance for last day of week.
I have daily transaction movements for each day of week.
I wish to calculate the Opening and Closing balance for each day in the script and store as a QVD.
Sample data attached
Thanks
Dave
One solution is.
tab1:
LOAD *, [Closing Bal]-Movement As [Opening Bal]
;
LOAD Date, Movement, If(RowID>1, Peek([Closing Bal])-Peek(Movement),[Closing Bal]) As [Closing Bal]
;
LOAD *, RecNo() As RowID INLINE [
Date, Opening Bal, Movement, Closing Bal
1/7/2020, , 70, 1000
1/6/2020, , 60,
1/5/2020, , -50,
1/4/2020, , 40,
1/3/2020, , -30,
1/2/2020, , 20,
1/1/2020, , -10,
];
One solution is.
tab1:
LOAD *, [Closing Bal]-Movement As [Opening Bal]
;
LOAD Date, Movement, If(RowID>1, Peek([Closing Bal])-Peek(Movement),[Closing Bal]) As [Closing Bal]
;
LOAD *, RecNo() As RowID INLINE [
Date, Opening Bal, Movement, Closing Bal
1/7/2020, , 70, 1000
1/6/2020, , 60,
1/5/2020, , -50,
1/4/2020, , 40,
1/3/2020, , -30,
1/2/2020, , 20,
1/1/2020, , -10,
];
thanks very much.
works a treat
Dave
you welcome