Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
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
Highlighted
Specialist
Specialist

Re: Calculate Opening and Closing Balances and store in QVD

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

Highlighted
Specialist
Specialist

Re: Calculate Opening and Closing Balances and store in QVD

4 Replies
Highlighted
Specialist
Specialist

Re: Calculate Opening and Closing Balances and store in QVD

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

Highlighted
Specialist
Specialist

Re: Calculate Opening and Closing Balances and store in QVD

Highlighted
Creator
Creator

Re: Calculate Opening and Closing Balances and store in QVD

thanks very much.

works a treat

Dave

Highlighted
Specialist
Specialist

Re: Calculate Opening and Closing Balances and store in QVD

you welcome