Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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;
Hi Olle, probably [Settlement timestamp] has many different values for the same date (different time), try using floor to avoid time differences.
order the table before you try to create the running balance. In a step before that.
Hello,
Looks correct.
Try peek(SettlementDate) instead of previous there too. Hints as there is problem with if's condition.
BR
Serhan
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
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
completely agree. You can peek the field, no problem with that.
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.
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
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.
Hi Olle, probably [Settlement timestamp] has many different values for the same date (different time), try using floor to avoid time differences.