topic Re: Balances per day in QlikView

Balances per day

Tue, 02 Oct 2012 07:34:23 GMT

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

Re: Balances per day

Anonymous — Tue, 02 Oct 2012 08:23:25 GMT

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;

Re: Balances per day

Tue, 02 Oct 2012 09:08:30 GMT

That worked! Thanks Johannes!