Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Live chat with experts, bring your API Integration questions. June 15th, 10 AM ET. REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Ordering a joined and concatenated table (plus running balance)

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:

  1. Sort the table using Date
  2. Create a new column called Balance which is Amount + peek(Balance)

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.

2 Replies
Gysbert_Wassenaar

Try changing the last load statement to:

Load *,

Amount + alt(peek('Balance'),0) as Balance,

Resident Facts2 order by Date;

drop table Facts2;


talk is cheap, supply exceeds demand
Not applicable
Author

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