Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi Everyone,
I have two fields date and blnc
Date | Balance |
---|---|
1/04/2014 | 856 |
2/04/2014 | 147 |
3/04/2014 | 259 |
4/04/2014 | 325 |
8/04/2014 | 693 |
9/04/2014 | 258 |
I have to add two new field CurrentAcc and InitialAcc.
initially InitialAcc=0,
and Expression for CurrentAcc=Balance+InitialAcc.
And for next date InitialAcc=CurrentAcc.
I know I have to use a loop statements but how I use the expression in the back end. If anyone has any idea do share with me.
Thank you in advance.
Temp:
Load
RowNo() as NO,
Date(Date#(Date,'D/MM/YYYY')) as Date,
Balance
Inline
[
Date, Balance
1/04/2014, 856
2/04/2014, 147
3/04/2014, 259
4/04/2014, 325
8/04/2014, 693
9/04/2014, 258
];
Final:
Load
NO,
Date,
Balance,
If(RowNo() = 1, 0, Peek(CurrentAcc)) as InitAcc,
If(RowNo() = 1, Balance, Peek(CurrentAcc)+Balance) as CurrentAcc
Resident Temp;
Drop Table Temp;
use the RowNo() and Peek() functions, the examples are quite good it
But how I store the value in the field,do you have any script idea for it. since I am very new to qlikview so I didnt get the right way to do.
Use below script..
===================
Temp:
Load
RowNo() as NO,
Date(Date#(Date,'D/MM/YYYY')) as Date,
Balance
Inline
[
Date, Balance
1/04/2014, 856
2/04/2014, 147
3/04/2014, 259
4/04/2014, 325
8/04/2014, 693
9/04/2014, 258
];
Final:
Load
NO,
Date,
Balance,
If(RowNo() = 1, Balance, Peek(CurrentAcc)+Balance) as CurrentAcc
Resident Temp
Order By NO;
Drop Table Temp;
//Drop Field NO
=====================================
Try this..
RangeSum(Above(Sum(Balance), 0, RowNo()))...
Selva
Date | Balance | InitAcc | CurrAcc |
---|---|---|---|
1/04/2014 | 856 | 0 | 856 |
2/04/2014 | 147 | 856 | 1003 |
3/04/2014 | 259 | 1003 | 1262 |
4/04/2014 | 325 | 1262 | 1587 |
8/04/2014 | 693 | 1587 | 2280 |
9/04/2014 | 258 | 2280 | 2538 |
This script is not giving the right answer. I want a new table with above field..
Temp:
Load
RowNo() as NO,
Date(Date#(Date,'D/MM/YYYY')) as Date,
Balance
Inline
[
Date, Balance
1/04/2014, 856
2/04/2014, 147
3/04/2014, 259
4/04/2014, 325
8/04/2014, 693
9/04/2014, 258
];
Final:
Load
NO,
Date,
Balance,
If(RowNo() = 1, 0, Peek(CurrentAcc)) as InitAcc,
If(RowNo() = 1, Balance, Peek(CurrentAcc)+Balance) as CurrentAcc
Resident Temp;
Drop Table Temp;
Manish has the answer you are looking for
Temp:
Load
RowNo() as NO,
Date(Date#(Date,'D/MM/YYYY')) as Date,
Balance
Inline
[
Date, Balance
1/04/2014, 856
2/04/2014, 147
3/04/2014, 259
4/04/2014, 325
8/04/2014, 693
9/04/2014, 258
];
Final:
Load
NO,
Date,
Balance,
If(RowNo() = 1, 0, Peek(CurrentAcc)) as InitAcc,
If(RowNo() = 1, Balance, Peek(CurrentAcc)+Balance) as CurrentAcc
Resident Temp;
Drop Table Temp;