Set Variables using excel file

[gerhardl]

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.

[alexandros17]

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

[yduval75 · Accepted solution]

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;

[stevedark]

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