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
