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;
