Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Not applicable

Balances per day

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.

keytrans_amttrans_date
14002012-07-01
1-202012-07-03
1-802012-07-03
1-3002012-07-05
26002012-07-10
2-6002012-07-10
21002012-07-13
2-1002012-07-14

I would like the result to look like this.

Expected result
Keydatebalance
12012-07-01400
12012-07-03300
12012-07-050
22012-07-13100
22012-07-140

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

Cheers

Anna

1 Solution

Accepted Solutions
jsn
Honored Contributor

Re: Balances per day

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;

2 Replies
jsn
Honored Contributor

Re: Balances per day

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;

Not applicable

Re: Balances per day

That worked! Thanks Johannes!

Community Browser