Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements

Breathe easy -- you now have more time to plan your next steps with Qlik!
QlikView 11.2 Extended Support is now valid through December 31, 2020. Click here for more information.

Not applicable

Building a balance history table

Dear All,

I am start getting fedup from QlickView and questioning if it is the right tool, or may we need more training???

any way, I have the following scenario that need your support

- I have a balance table of one record per account with last transaction date

  i.e.

  A/C # 1, Balance, Last transaction date

  A/C # 2, Balance, Last transaction date

  example

  3454, 9200Cr, 23/4/2011

  4213, 5400Dr, 20/4/2011

also, I have a transactional table that list all the transactions for each account as the followig

account,transaction date, amount, other fields

3454, 22/3/2010 , 13000cr,

3454, 22/3/2011 , 8000cr,

3454, 22/3/2011 , 2000cr,

3454, 02/4/2011 , 4000dr,

3454, 09/4/2011, 1000dr,

3454, 23/4/2011, 500cr,

I need to build a blanace file with variance in the balance with a starting balance of each month since the begining of a year and with records for the date where the balance change by using these 2 tables so my final history table should look like

A/C, Data, Balance, Variance in amount , number of days between transactions, balance held till date, no of days of holding this balance

3454, 1/1/2011  , 4000cr (i.e. 9500cr - (sum of 8000+2000+-4000+-1000+500)  , 0 , 0 , 0 , 0

3454, 1/2/2011  , 4000cr (i.e. 4000+ sum(transactions in Jan 2011)  , 0 , 31 , 4000 , 31

3454, 1/3/2011  , 4000cr (i.e. 4000+ sum(transactions in Jan 2011)  , 0 , 28 , 4000 , 28

3454, 22/3/2011, 14000cr (i.e. 4000+ 8000+2000)  ,10000, 20 , 4000 , 20 

3454, 01/4/2011, 14000cr (i.e. 4000+ 8000+2000)  ,0, 8 , 14000 , 8

3454, 02/4/2011, 10000cr (i.e. 14000-4000) ,-4000, 1 , 14000 , 1

3454, 09/4/2011, 9000cr (i.e. 10000-1000) ,-1000, 7 , 10000 , 7

3454, 23/4/2011, 9500cr (i.e. 9000+500) ,+500, 14 , 9000, 14

I am not sure if QlickView can manage this, or should I write a complete program to do this .... any help out there

Thanks 

Community Browser