Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
wgonzalez
Partner
Partner

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


 

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
Luminary Alumni
Luminary Alumni

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


 

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

wgonzalez
Partner
Partner
Author

Thanks.  That worked.

wgonzalez
Partner
Partner
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
Partner
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.