Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
bismart
Creator
Creator

Calculate Opening and Closing Balances and store in QVD

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

bismart_0-1590237930761.png

 

Labels (5)
2 Solutions

Accepted Solutions
Saravanan_Desingh

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, 
];

View solution in original post

4 Replies
Saravanan_Desingh

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, 
];
Saravanan_Desingh

commQV68.PNG

bismart
Creator
Creator
Author

thanks very much.

works a treat

Dave

Saravanan_Desingh

you welcome