Calculating running balance by PEEK function

[#1]

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;

[giakoum]

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

[Anonymous]

Hello,

Looks correct.

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

BR

Serhan

[#4]

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

[Anonymous]

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.

BR

Serhan

[giakoum]

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

[jagan]

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.

[#8]

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

[jagan]

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.

[rubenmarin · Accepted solution]

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