0 Replies Latest reply: Jul 7, 2011 6:28 AM by asnajim2000 RSS

    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