Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
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

Saravanan_Desingh

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