Generating data in QV

wgonzalez — Sat, 16 Nov 2024 21:27:31 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).

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.

Re: Generating data in QV

mikaelsc — Wed, 06 Feb 2019 19:55:24 GMT

autogenerate first your complete data table... then join...

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
while ($(vStartYear)+ IterNo() -1) <= $(vEndYear);

Join
LOAD

****

then again, that's only the script issue... still thinking about the actual problem 🙂

topic Re: Generating data in QV in QlikView

Generating data in QV

Re: Generating data in QV