Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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;
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;
That worked! Thanks Johannes!