Discussion Board for collaboration on QlikView Scripting.
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
SQL ... From ...;
Left join (Facts)
Left join (Facts2)
This creates the required table, however I'm not sure how I can:
Any help will be much appreciated.
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:
Amount + alt(peek('Balance'),0) as Balance,
Resident Facts2 order by Date;
drop table Facts2;
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...