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
