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: 
paul_ripley
Creator III
Creator III

Loading variables into Qlikview - period hard coded

Hello

I have a spreadsheet full of variables, which I load into Qlikview using 

$(Must_Include=..\..\06_sys\config\Template_STD_Variables.txt);

Template_STD_Variables.txt    contains the following script

VariableList:
LOAD VName,
VContent
FROM
[..\..\06_Sys\Config\Variable List.xlsx]
(ooxml, embedded labels, table is Sheet1)
Where not isnull(VName);

For vCount = 0 to NoOfRows('VariableList')-1
Let vName=Peek('VName',vCount,'VariableList');
Let vContent=Peek('VContent',vCount,'VariableList');
Let $(vName)='$(vContent)';
Next vCount
Let vCount=null();
Let vContent=null();
Let vName=null();
Drop Table VariableList;

 

The issue is that I am unable to amend the periods in the app after they have loaded.

For example this is the variable that I want to load into the app

 

=sum({$<period ={">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, pl_line_mgmt = {'Gross investment return'}, bussline_abrev = {'PE'}>} IS_Investment_Adj_GBP) *-1
+
sum({$<period ={">=$(vStartPeriod_IS_CY)<=$(vEndPeriod_CY)"}, pl_line_mgmt = {'Gross investment return'}, bussline_abrev = {'PE'}>} IS_Investment_GBP)  *-1

What actually is in the variable in the app is ie the periods are hardcopded and cant be changed in the app.

How can i just load the variable without the periods in them

 

=sum({$<period ={">=201801<=201806"}, pl_line_mgmt = {'Gross investment return'}, bussline_abrev = {'PE'}>} IS_Investment_Adj_GBP) *-1
+
sum({$<period ={">=201801<=201806"}, pl_line_mgmt = {'Gross investment return'}, bussline_abrev = {'PE'}>} IS_Investment_GBP) *-1

Many thanks

Labels (2)
1 Solution

Accepted Solutions
Vegar
MVP
MVP

Try to avoid the use of the vContent to temporary store the expression code, but instead do the peek() when defining the variable like this:

 

VariableList:
LOAD VName,
  VContent
FROM
  [..\..\06_Sys\Config\Variable List.xlsx]
  (ooxml, embedded labels, table is Sheet1)
Where not isnull(VName);

For vCount = 0 to NoOfRows('VariableList')-1
  Let vName=Peek('VName',vCount,'VariableList');
  Let $(vName)=Peek('VContent',vCount,'VariableList');
  //Let $(vName)='$(vContent)';
Next vCount
Let vCount=null();
Let vContent=null();
Let vName=null();
Drop Table VariableList;

View solution in original post

2 Replies
Vegar
MVP
MVP

Try to avoid the use of the vContent to temporary store the expression code, but instead do the peek() when defining the variable like this:

 

VariableList:
LOAD VName,
  VContent
FROM
  [..\..\06_Sys\Config\Variable List.xlsx]
  (ooxml, embedded labels, table is Sheet1)
Where not isnull(VName);

For vCount = 0 to NoOfRows('VariableList')-1
  Let vName=Peek('VName',vCount,'VariableList');
  Let $(vName)=Peek('VContent',vCount,'VariableList');
  //Let $(vName)='$(vContent)';
Next vCount
Let vCount=null();
Let vContent=null();
Let vName=null();
Drop Table VariableList;

paul_ripley
Creator III
Creator III
Author

thanks so much that worked and is super helpful!