Script to add column to table

[#1]

Hi,

I need to add a new field to and existing table (shown below). The field is calculated using values from the previous record. The problem is that the new field is part of the formula.

The following script gives me this error: '[New Field] does not exist'. Any help would be appreciated.

T1:

Load

          *,

  if(PeriodID = 1, 1, Previous('[New Field]')-Previous('[Budget Contr]')) as [New Field]

Resident TableName;

PeriodID	Period	Budget Amt	Budget Contr	New Field
1	Apr	145886.00	0.0724	1.0000
2	May	178932.00	0.0888	0.9276
3	Jun	174902.00	0.0868	0.8388
4	Jul	182962.00	0.0908	0.7520
5	Aug	188201.00	0.0934	0.6612
6	Sep	178932.00	0.0888	0.5678
7	Oct	198880.50	0.0987	0.4790
8	Nov	196261.00	0.0974	0.3803
9	Dec	132587.00	0.0658	0.2829
10	Jan	132587.00	0.0658	0.2171
11	Feb	145886.00	0.0724	0.1513
12	Mar	158983.50	0.0789	0.0789

[jolivares · Accepted solution]

Try this:

T1:

Load *,

     If(PeriodID = 1, 1, Peek([New Field])-Previous('[Budget Contr]')) as [New Field]

Resident TableName;