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.
I have checked your example; unfortunately it doesnt produce a line for every date.
In the example I posted, every line in the Account data should be duplicated in line with the number of dates going from 31/1/11 right through to 31/12/12.
This is what the result would be if I did a join between the two tables; as there is no 'Date' field in the account table, each line would be duplicated (which is the result I am after).
I just cant get this working with a large data set.
