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: 
Rizzo
Contributor II
Contributor II

Need to count the balance field

OK, guys, I have stuck and without your help I'll won't move forward :(.
What I need is in my load script to count the balance field . 
I hope you will understand the calculation logic from the picture 🙂

Rizzo_1-1616705531194.png

Table:
LOAD * Inline [

Date, No, Location, Quantity

2021-01-01, A1235, M01, 3
2021-01-02, B1235, M02, 2
2021-02-03, A1235, M01, -1
2021-03-03, B1235, M02, 1
2021-03-04, C1235, M03, 2
2021-03-04, A1235, M01, -1

];

 

 

1 Solution

Accepted Solutions
QFabian
Specialist III
Specialist III

Hi @Rizzo , please add this script after your inline, and see if it works for you :

//the trick is order by Location field, to simplify the sum between rows.


Table2:
Load
*,
if(rowno()=1, Quantity,
if(previous(Location) = Location, Quantity + peek(Balance), Quantity)) as Balance;
Load
Date,
No,
Location,
Quantity
Resident Table
order by Location, Date;
drop table Table;

QFabian

View solution in original post

2 Replies
QFabian
Specialist III
Specialist III

Hi @Rizzo , please add this script after your inline, and see if it works for you :

//the trick is order by Location field, to simplify the sum between rows.


Table2:
Load
*,
if(rowno()=1, Quantity,
if(previous(Location) = Location, Quantity + peek(Balance), Quantity)) as Balance;
Load
Date,
No,
Location,
Quantity
Resident Table
order by Location, Date;
drop table Table;

QFabian
Rizzo
Contributor II
Contributor II
Author

This is what I need.
Thank you!
And what would the script look like in set analysis if I wanted to get the same result?