Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I'm trying to generate data for four years ahead, for the data already loaded in another table (preceding load). The idea is to loop and generate data of four years ahead for each row in the previously loaded table which is source to the while loop.
I'm receiving the "field not found error", nevertheless the source table is already loaded with the needed fields. Please take a look at script below to see what could be wrong. Thanks.
ActualsTemp_1:
LOAD
KeyToActuals
,FLMCU
,FLOBJ
,([FLCTRY]*100)+ [FLFY] as FYear
,FLAN01/100 as 01
,FLAN02/100 as 02
,FLAN03/100 as 03
,FLAN04/100 as 04
,FLAN05/100 as 05
,FLAN06/100 as 06
,FLAN07/100 as 07
,FLAN08/100 as 08
,FLAN09/100 as 09
,FLAN10/100 as 10
,FLAN11/100 as 11
,FLAN12/100 as 12
Resident $(vInputActuals);
/* autogenerate records for 4 years ahead */
let vZero = 0;
Let vStartYear = (year(Today())+1);
Let vEndYear = (year(today())+4);
Auto_Generated_Actuals:
LOAD
KeyToActuals
,FLMCU
,FLOBJ
,$(vStartYear) + (RowNo() -1) as FYear
,$(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;
LOAD
KeyToActuals
,FLMCU
,FLOBJ
Resident ActualsTemp_1
while ($(vStartYear)+ IterNo() -1) <= $(vEndYear);
let vZero = 0;
Let vStartYear = (year(Today())+1);
Let vEndYear = (year(today())+4);
Auto_Generated_Actuals:
LOAD
KeyToActuals,FLMCU,FLOBJ
$(vStartYear) + (RowNo() -1) as FYear
,$(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
KeyToActuals
,FLMCU
,FLOBJ
Resident ActualsTemp_1while ($(vStartYear)+ IterNo() -1) <= $(vEndYear);
I'm not completely following what you are trying to accomplish, but your syntax problem is:
AutoGenerate 1;
AutoGenerate is a source. Your fields
KeyToActuals
,FLMCU
,FLOBJ
will not be passed up from the lower load.
-Rob
let vZero = 0;
Let vStartYear = (year(Today())+1);
Let vEndYear = (year(today())+4);
Auto_Generated_Actuals:
LOAD
KeyToActuals,FLMCU,FLOBJ
$(vStartYear) + (RowNo() -1) as FYear
,$(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
KeyToActuals
,FLMCU
,FLOBJ
Resident ActualsTemp_1while ($(vStartYear)+ IterNo() -1) <= $(vEndYear);
Thanks. That worked.
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).
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.
Hi,
This change worked fine, nevertheless the FYear is not being incremented as expected. It stays fixed at the result of Let vStartYear = (year(Today())+1) which is 2020.
Any suggestion is appreciated.