Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
Leverage your QlikView investment to modernize BI – see how! Join Group
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Creator
Creator

Generating data in QV

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:
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.

1 Reply
Highlighted
Creator III
Creator III

Re: Generating data in QV

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 🙂