Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have to join and concatenate 4 source tables to produce one Facts table. I would then like to sort the Facts table based on Date field so I can produce a running balance field.
So far I have
Facts:
Load
Date,
...;
SQL ... From ...;
Left join (Facts)
Load
Amount,
...;
SQL ... From ...;
Facts2:
Load
Date,
...;
SQL ... From ...;
Left join (Facts2)
Load
Amount,
...;
SQL ... From ...;
Concatenate (Facts)
Load
*
Resident Facts2;
Drop Facts2;
This creates the required table, however I'm not sure how I can:
Any help will be much appreciated.
Matt
Clarification by: matt.lj.morris
So Thanks to Gysbert I have now got my data ordered by Date, but I'm having lots of problems calculating the running balance.
My four attempts are:
Amount + alt( previous(Amount),0 ) as Balance1
if( isnull( previous(Amount) ), Amount, Amount + Peek(Balance2) ) As Balance2
numsum(Amount,peek(Balance3)) AS Balance3
if( rowno()=0, Amount, Amount + peek(Balance4) ) as Balance4
And the results of each are in the attached image.
Try changing the last load statement to:
Load *,
Amount + alt(peek('Balance'),0) as Balance,
Resident Facts2 order by Date;
drop table Facts2;
Dear Gysbert,
Thanks for your speedy response. Previewing the Facts table I see that it has ordered the rows by date but there doesn't seem to be any values in Balance...
Matt