topic Variables (with SA) declaration from excel file in QlikView

Variables (with SA) declaration from excel file

blaise — Wed, 26 Nov 2014 13:18:05 GMT

Hi fellow Developers

I have struggled for some time now to find the best way to declare my variables i use for expressions in an excel workbook. I have issues with some expressions that includes some Set Analysis, for example;

median({$<Dim1 = {'C'}, Dim2 = {"$(=maxstring(Dim2))"}>} Expression1)

Im trying to declare a variable (using LET) but the Dim2 Set Analysis part fails and the end-result looks like;

median({$<Dim1 = {'C'}, Dim2 = {""}>} Expression1)

I would rather now have to type the variables in Excel using other characters than "(", ")", "'" and "i".

Have anyone found a good way to do this or is the best way to declare them in the GUI (ctrl+alt+v) and then create a macro that replaces some characters and store them in excel and a load script that replaces the replaced characters with the correct ones?

Until I find a good way without to many replace() functions when declaring the variables i guess the best way is to only delcare the variables in the GUI and just have an excel file with all variables as a backup.

Re: Variables (with SA) declaration from excel file

Wed, 26 Nov 2014 13:23:36 GMT

I use an excel sheet to define variables, but I don't know if this is exactly what you mean.

My excel sheet contains 3 culmns: variable name, variable definition and a description (last one is not necessary, for documentation purpose only).

The following script reads the Excel sheet and defines al variables it finds:

// Script used to load standard variables from an excel sheet
// Can be used to re-use variable definitions and standard formulas
// The excel sheet used is 'QlikviewVariables.xls'


QV_variables:
LOAD Name, 
     Expression, 
     Description
FROM
'..\Scripts\QlikviewVariables.xls'
(biff, embedded labels, table is QV_variables$);


Let vCount = NumMax(NoOfRows('QV_variables'),0)-1;


FOR i=0 to vCount
  Let TempVarName = peek('Name',$(i),'QV_variables');
  Let TempVarValue = peek('Expression',$(i),'QV_variables');
  Let $(TempVarName) = '$(TempVarValue)';
NEXT


//cleanup
Set vCount = ;
Set TempVarName = ;
Set TempVarValue = ;
Set i = ;


DROP Table QV_variables;

Re: Variables (with SA) declaration from excel file

blaise — Wed, 26 Nov 2014 14:18:54 GMT

Does mine expression (or a similiar one) works with your code? I'm having issues with my set analysis max filter. When I do a peek to store the variable everything between the two " is removed

Re: Variables (with SA) declaration from excel file

marcus_sommer — Wed, 26 Nov 2014 14:25:49 GMT

The method from Dick Zeeman worked with SA expressions, too. Alternatively have a look here: Re: Setting a variable in script...

- Marcus

Re: Re: Variables (with SA) declaration from excel file

blaise — Wed, 26 Nov 2014 15:03:52 GMT

Found the issue with my code!

I was doing one peek() to fetch the variable Name and then one more peek() to fetch the Variable Expression/Declaration string. This was followed by the actual variable declaration;

Let '$(vVarName)' = '$(vVarExp)';

By doing the peek() to fetch the variable expression when declaring the variable i got it to work;

Variables:

LOAD

varName,

varExp

FROM

[variables.xlsx]

(ooxml, embedded labels, table is Sheet1)

;

for i = 0 to noofRows('variables') - 1

let vVarName = peek('varName',$(i),'variables');

let '$(vVarName)' = peek('varExp',$(i),'variables'); // <-- this one works!

Let vVarExp = peek('varExp',$(i),'variables'); // <-- this one does not!

let '$(vVarName)' = '$(vVarExp)';

next i;

Thanks for the replies!