Balances per day

[#1]

Hi,

I am trying to calculate balances per day per "key". But I only need the balance to be calculated when the balance changes for one key (to limit the amount of data). Furthermore I want to do the calculation in the script. The table below shows the transactions for two different keys.

key	trans_amt	trans_date
1	400	2012-07-01
1	-20	2012-07-03
1	-80	2012-07-03
1	-300	2012-07-05
2	600	2012-07-10
2	-600	2012-07-10
2	100	2012-07-13
2	-100	2012-07-14

I would like the result to look like this.

Expected result
Key	date	balance
1	2012-07-01	400
1	2012-07-03	300
1	2012-07-05	0
2	2012-07-13	100
2	2012-07-14	0

Any idéa how I can solve this? All help is appriciated!

Cheers

Anna

[Anonymous · Accepted solution]

T1:

LOAD key,

     sum(trans_amt) as balance,

     trans_date

FROM

C:\QlikViewDocs\OwnTesting\rawdata.txt

(txt, codepage is 1252, embedded labels, delimiter is '\t', msq)

group by key, trans_date;

NoConcatenate

Load

          key,

          trans_date,

          if(peek(key)<>key,balance,peek(balance)+balance) as balance

resident T1

order by key, trans_date;

drop table T1;

[#3]

That worked! Thanks Johannes!