3 Replies Latest reply: Nov 27, 2012 3:12 PM by Fernando Suzuki RSS

    Table Help

      Hi,

       

      I am currently trying to manipulate a large table to prepare it for calculations within the load script.  It involves duplicating each line of data with the addition of a column with a specific date as the field.  The best way I thought about doing this was by joining a date table to my main table (no links involved).

       

      I have attached an example that works (to a degree)

       

      Accounts:

       

      Ref

       

       

      AccntRef

       

       

      Start

       

       

      End

       

       

      Transaction

       

       

      Amount

       

       

      AAA

       

       

      1

       

       

      01/07/2010

       

       

      30/06/2011

       

       

      01/07/2010

       

       

      5000

       

       

      BBB

       

       

      2

       

       

      02/08/2010

       

       

      01/08/2011

       

       

      02/08/2010

       

       

      10000

       

       

      AAA

       

       

      3

       

       

      01/07/2010

       

       

      30/06/2011

       

       

      31/12/2010

       

       

      250

       

       

      BBB

       

       

      4

       

       

      02/08/2010

       

       

      01/08/2011

       

       

      31/12/2010

       

       

      500

       

       

      AAA

       

       

      5

       

       

      01/07/2010

       

       

      30/06/2011

       

       

      15/02/2011

       

       

      100

       

       

      BBB

       

       

      6

       

       

      02/08/2010

       

       

      01/08/2011

       

       

      15/02/2011

       

       

      600

       

       

      AAA

       

       

      7

       

       

      01/07/2011

       

       

      30/06/2012

       

       

      01/07/2011

       

       

      7500

       

       

      BBB

       

       

      8

       

       

      02/08/2011

       

       

      01/08/2012

       

       

      02/08/2011

       

       

      15000

       

       

      AAA

       

       

      9

       

       

      01/07/2011

       

       

      30/06/2012

       

       

      31/12/2011

       

       

      500

       

       

      BBB

       

       

      10

       

       

      02/08/2011

       

       

      01/08/2012

       

       

      31/12/2011

       

       

      1000

       

       

      AAA

       

       

      11

       

       

      01/07/2011

       

       

      30/06/2012

       

       

      15/02/2012

       

       

      600

       

       

      BBB

       

       

      12

       

       

      02/08/2011

       

       

      01/08/2012

       

       

      15/02/2012

       

       

      900

       

       

       

      Dates:

       

      Date

       

      31/01/2011

       

      28/02/2011

       

      31/03/2011

       

      30/04/2011

       

      31/05/2011

       

      30/06/2011

       

      31/07/2011

       

      31/08/2011

       

      30/09/2011

       

      31/10/2011

       

      30/11/2011

       

      31/12/2011

       

      31/01/2012

       

      28/02/2012

       

      31/03/2012

       

      30/04/2012

       

      31/05/2012

       

      30/06/2012

       

      31/07/2012

       

      31/08/2012

       

      30/09/2012

       

      31/10/2012

       

      30/11/2012

       

      31/12/2012

       

       

      The attached application gives the result as expected.  The issue is that when I apply this to large data sets; the whole application 'falls over'.   Is there a better way I can do this?

       

      In terms of the calculation I need to look at the 'Amount' and use a combinatuion of 'Start' date and 'Date' (added field) to arrive at my answer.  I have tried using a pivot table in the main application; this falls over as well.

       

       

       

      Many thanks