Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
wgonzalez
Partner - Creator
Partner - Creator

Field not found using preceding load to autogenerate data

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);

Labels (4)
1 Solution

Accepted Solutions
Anil_Babu_Samineni



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_1

while ($(vStartYear)+ IterNo() -1) <= $(vEndYear);


 

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)

View solution in original post

5 Replies
rwunderlich
Partner Ambassador/MVP
Partner Ambassador/MVP

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

Anil_Babu_Samineni



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_1

while ($(vStartYear)+ IterNo() -1) <= $(vEndYear);


 

Please add me Anil_Babu_Samineni to interact faster when reply back. Speak low think High.

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
wgonzalez
Partner - Creator
Partner - Creator
Author

Thanks.  That worked.

wgonzalez
Partner - Creator
Partner - Creator
Author

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:
AssetNoAssetNo
DEndYearBU
BUAcct
AcctYear
 M01
 M02
 M03

 

I've tried script construct using the while statement, but it hasn't worked.  Any advise is appreciated.

wgonzalez
Partner - Creator
Partner - Creator
Author

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.