Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi,
I currently set variables at the start of my load script like this:
Set vStore1 = '1036' ;
Set vStore2 = '1179' ;
Set vStore3 = '0980' ;
I would like to keep my variables in an excel file, with these columns (see image attached):
Variable Name, Store Number
I know how to do this from a text file, but the idea is for users to go to the Excel File and change the stores which are loaded into the module.
Temp_Variables:
LOAD
[Variable Name],
[Store Number]
FROM ............\Variables.xls]
(biff, embedded labels, table is [Sheet1$]);
for i = 0 to NoOfRows('Temp_Variables') - 1
let vName = peek('Variable Name', i, 'Temp_Variables'); // Name of the variable
let $(vName) = peek('Store Number', i, 'Temp_Variables'); // Number of the variable
next i
let i = null();
let vName = null();
drop table Temp_Variables;
Paste this in your script:
VARL: NoConcatenate LOAD VarName, VarSetting FROM TEST_Sonia.xlsx (ooxml, embedded labels, table is LET);
VARS: NoConcatenate LOAD VarName, VarSetting FROM TEST_Sonia.xlsx (ooxml, embedded labels, table is SET);
CONN: NoConcatenate LOAD ODBCName FROM Data.xlsx (ooxml, embedded labels, table is ODBC);
FOR idx = 0 to NoOfRows('VARL')-1; LET VN = Peek('VarName', idx, 'VARL'); LET VS = Peek('VarSetting', idx, 'VARL'); LET $(VN) = $(VS) ; NEXT
FOR idx = 0 to NoOfRows('VARS')-1; LET VN = Peek('VarName', idx, 'VARS'); LET VS = Peek('VarSetting', idx, 'VARS'); SET $(VN) = '$(VS)'; NEXT
FOR idx = 0 to NoOfRows('CONN')-1; LET DB = Peek('ODBCName', idx, 'CONN'); ODBC CONNECT to $(DB); NEXT
DROP Tables VARL, VARS, CONN;
Where Data.xlsx is the attachment
Hope it helps
Temp_Variables:
LOAD
[Variable Name],
[Store Number]
FROM ............\Variables.xls]
(biff, embedded labels, table is [Sheet1$]);
for i = 0 to NoOfRows('Temp_Variables') - 1
let vName = peek('Variable Name', i, 'Temp_Variables'); // Name of the variable
let $(vName) = peek('Store Number', i, 'Temp_Variables'); // Number of the variable
next i
let i = null();
let vName = null();
drop table Temp_Variables;
Tim Benoit did a couple of excellent blog posts where he showed how Excel macros could be used to interact with the QlikView API to get and set variable values. I recommend searching for this.
You will sometimes run into problems with quotes and $ symbols in your variables values in a spreadsheet - you may find you need to do a SUBSITUTE or two in Excel and the corresponding REPLACE statements in QlikView to switch the problem characters for characters that are not generally used (¬ and ~ are the ones I use).
Steve