Running balance BY ID

[mario-sarkis]

Dear QLikers,

In need to calculate the running balance by ID , Below is an example of the output needed:

CUSTOMER_ID	Amount	Running Balance
1	158,393	158,393
1	1,000,000	1,158,393
1	(400,000)	758,393
1	(100,000)	658,393
1	(68,000)	590,393
1	(2,000)	588,393
1	500,000	1,088,393
1	(279,000)	809,393
1	(877,000)	(67,607)
1	(10,000)	(77,607)
1	100,000	22,393
1	(134)	22,258
2	6,207,334	6,207,334
2	3,560,000	9,767,334
2	5,600,000	15,367,334
2	(1,651)	15,365,682
2	(11,500,000)	3,865,682
2	(1,000)	3,864,682
2	(3,860,000)	4,682
2	(1,000)	3,682
2	6,800,000	6,803,682
2	1,318,000	8,121,682
2	(750,000)	7,371,682
2	7,093,000	14,464,682
2	(1,000)	14,463,682
2	(14,460,000)	3,682
2	(1,000)	2,682

Is it possible to do it in the script? and I need the expression using set analysis incase in case I need to calculated using straight table.

Thank you in advance.

Mario

[Taoufiq_Zarra · Accepted solution]

Hi ,

One solution :

if(rowno()=1 or CUSTOMER_ID<>peek(CUSTOMER_ID),Amount,peek([Running Balance])+Amount) as [Running Balance]

example :

LOAD *,if(rowno()=1 or CUSTOMER_ID<>peek(CUSTOMER_ID),Amount,peek([Running Balance])+Amount) as [Running Balance] INLINE [
    CUSTOMER_ID, Amount
    1,  158393
    1,  1000000
    1,  -400000
    1,  -100000
    1,  -68000
    1,  -2000
    1,  500000
    1,  -279000
    1,  -877000
    1,  -10000
    1,  100000
    1,  -134
    2,  6207334
    2,  3560000
    2,  5600000
    2,  -1651
    2, -11500000
    2,  -1000
    2,  -3860000
    2,  -1000
    2,  6800000
    2,  1318000
    2,  -750000
    2,  7093000
    2,  -1000
    2, -14460000
    2,  -1000
];

output :