topic Re: Calculating running balance by PEEK function in QlikView

Calculating running balance by PEEK function

Wed, 11 Feb 2015 15:08:55 GMT

Hi,

This one:

If(SettlementDate <> Previous(SettlementDate),One_Amount,One_Amount+Peek(RunningBalance)) As RunningBalance

Should be showing the accumulated position but just shows the same value as the transaction when I try.

anyone got an idea why the below does not work for me?

Thanks in advance,

Olle

TEMP:
LOAD
        Site,
          [Amount of transaction (signed)],
           date([Settlement timestamp]) as SettlementDate,
          Text(Time(round([Settlement timestamp], (1/8640000)), 'hh:mm:ss:ffffff')) as SettlementTime

FROM
[XXX\*.xls]
(biff, embedded labels, header is 2 lines, table is @1) ;

//GROUP BY, IN ORDER TO GET ONE SUM PER SPECIFIC TIME (if multiple transactions with identical timestamps)

INITIAL:
NoConcatenate LOAD

SettlementDate,
SettlementTime,
Sum([Amount of transaction (signed)]) as One_Amount

Resident TEMP
Group by SettlementDate, SettlementTime;

//CALCULATE RUNNING BALANCE, ORDER BY DATE, then TIME.

FINAL:
NoConcatenate
LOAD
One_Amount,
SettlementDate,
SettlementTime,
If(SettlementDate <> Previous(SettlementDate),One_Amount,One_Amount+Peek(RunningBalance)) As RunningBalance

Resident INITIAL
Order By SettlementDate, SettlementTime;
DROP TABLE INITIAL;

left join (FINAL)
LOAD
SettlementDate,
SettlementTime,
concat(distinct [Participant BIC], ', ') as Site

Resident TEMP
Group By SettlementDate, SettlementTime ;
DROP Table TEMP;

Re: Calculating running balance by PEEK function

giakoum — Wed, 11 Feb 2015 15:24:14 GMT

order the table before you try to create the running balance. In a step before that.

Re: Calculating running balance by PEEK function

Anonymous — Wed, 11 Feb 2015 15:36:41 GMT

Hello,

Looks correct.

Try peek(SettlementDate) instead of previous there too. Hints as there is problem with if's condition.

Serhan

Re: Calculating running balance by PEEK function

Wed, 11 Feb 2015 16:15:13 GMT

Hi Olle,

with peek you need to specify the field name with single quotes around it.

Peek('[Field]')

Even if you change that though, you won't be able to peek RunningBalance like that, as it is only being created in that final load you are doing, you can't peek a field that doesn't exist yet.

Is it not the One_Amount field you want to peek?

One_Amount + Peek('One_Amount')

hope that helps

Joe

Re: Calculating running balance by PEEK function

Anonymous — Wed, 11 Feb 2015 20:24:20 GMT

Hello Joe,

Actually exactly the opposite. This is why peek() is so powerful. Because you can use it while you are just loading the table. Plus '' is not a must for peek.

Attached is an example.

Olle,

I'm sure if you break the problem, you'll solve it. You can also check my example.

Serhan

Re: Calculating running balance by PEEK function

giakoum — Thu, 12 Feb 2015 08:21:07 GMT

completely agree. You can peek the field, no problem with that.

Re: Calculating running balance by PEEK function

jagan — Thu, 12 Feb 2015 09:12:57 GMT

Hi,

Try this script

FINAL:
NoConcatenate
LOAD
One_Amount,
SettlementDate,
SettlementTime,
If(Date(SettlementDate) <> Date(Peek('SettlementDate')),One_Amount, RangeSum(One_Amount, Peek('RunningBalance'))) As RunningBalance
Resident INITIAL
Order By SettlementDate, SettlementTime;

Hope it helps you.

Regards,

Jagan.

Re: Calculating running balance by PEEK function

Thu, 12 Feb 2015 09:25:33 GMT

Totally right guys, that's what I get for mixing up my Peek and Previous!

As to the quotes, you learn something new everyday, QV manual says peek needs a quoted literal, maybe I am reading an old copy

Re: Calculating running balance by PEEK function

jagan — Thu, 12 Feb 2015 09:29:33 GMT

Hi Joe,

Also you will get different results when you use Previous() when you use order by in the load statement. So better use Peek() always.

Regards,

Jagan.

Re: Calculating running balance by PEEK function

rubenmarin — Thu, 12 Feb 2015 10:05:46 GMT

Hi Olle, probably [Settlement timestamp] has many different values for the same date (different time), try using floor to avoid time differences.

Re: Calculating running balance by PEEK function

Thu, 12 Feb 2015 15:25:08 GMT

The floor made the trick, thanks!