topic Set Variables using excel file in QlikView

Set Variables using excel file

gerhardl — Fri, 04 Apr 2014 15:18:19 GMT

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.

Re: Set Variables using excel file

alexandros17 — Fri, 04 Apr 2014 15:25:10 GMT

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

Re: Set Variables using excel file

yduval75 — Fri, 04 Apr 2014 15:27:00 GMT

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;

Re: Set Variables using excel file

stevedark — Fri, 04 Apr 2014 15:52:50 GMT

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