Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Ordering a joined and concatenated table (plus running balance)

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

Re: Ordering a joined and concatenated table (plus running balance)

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

Community Browser