Building a balance history table

Thu, 07 Jul 2011 10:28:50 GMT

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

topic Building a balance history table in QlikView

Building a balance history table