Hi,
I need a script that creates forecast data. In other words, data that doesn't exist in any table so the user can estimate results.
For each row in the table Assets, I need to create several rows in table Forecast. The quantity of rows in table Forecast belonging to each row in table Assets will be determined by either field DEndYear or the variable vYearLimit whichever is lower. The value of variable vYearLimit will always be Year(Today()) + 4 (i.e. forecast 4 years ahead).
I'm currently using this script, but statements to produce fields FYear_TEST and FYear_TEST2 are not producing an incremental year number, which doesn't allow me to see if the script is working.
/* Generate Forecast records for 4 years ahead */
/* and automatic concatenation with ActualsTemp_1 */
let vZero = 0;
Let vStartYear = (year(Today())+1);
Let vEndYear = (year(today())+4);
Auto_Generated_Actuals:
LOAD
$(vStartYear) + (IterNo() -1) as FYear_TEST
,$(vStartYear) + (RowNo() -1) as FYear_TEST2
,$(vZero) as 01
,$(vZero) as 02
,$(vZero) as 03
,$(vZero) as 04
,$(vZero) as 05
,$(vZero) as 06
,$(vZero) as 07
,$(vZero) as 08
,$(vZero) as 09
,$(vZero) as 10
,$(vZero) as 11
,$(vZero) as 12
AutoGenerate 1;
Join
LOAD
[CO] as Company
,[Asset Number] as AssetNo
,[CurDeprExp BU] as CurBU
,[CurDeprExp Acct] as CurAcct
Resident $(vActiveAssets)
while ($(vStartYear)+ IterNo() -1) <= $(vEndYear);
These are the fields of tables:
| Assets: | Forecast: |
| AssetNo | AssetNo |
| DEndYear | BU |
| BU | Acct |
| Acct | Year |
| | M01 |
| | M02 |
| | M03 |
I've tried script construct using the while statement, but it hasn't worked. Any advise is appreciated.
