topic Re: Field not found using preceding load to autogenerate data in QlikView

Field not found using preceding load to autogenerate data

wgonzalez — Sat, 16 Nov 2024 21:28:14 GMT

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

Re: Field not found using preceding load to autogenerate data

rwunderlich — Tue, 05 Feb 2019 02:16:03 GMT

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

Re: Field not found using preceding load to autogenerate data

Anil_Babu_Samineni — Tue, 05 Feb 2019 04:55:47 GMT

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

Re: Field not found using preceding load to autogenerate data

wgonzalez — Tue, 05 Feb 2019 21:10:59 GMT

Thanks. That worked.

Re: Field not found using preceding load to autogenerate data

wgonzalez — Tue, 05 Feb 2019 22:01:29 GMT

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.

Re: Field not found using preceding load to autogenerate data

wgonzalez — Tue, 05 Feb 2019 22:53:58 GMT

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.