topic Re: Can anyone explain how this Variables script works please? in QlikView

Can anyone explain how this Variables script works please?

jblomqvist — Thu, 26 Nov 2015 14:39:44 GMT

Hello

Below is a script I found on the internet to load variables:

//====================================================================================================

// COMMON VARIABLES LOAD SCRIPT

// Sample script to load the common variables into the application.

// You may wish to store these in Excel (as was done here) or in a database or flat file. It won't

// impact the usage of the variables. Once they are read into the application here they are then

// created as document variables for this application with the second section of code below.

//====================================================================================================

CommonVariables:

LOAD

VariableName,

VariableValue

FROM

[Variables.xlsx]

(ooxml, embedded labels, table is Sheet1);

// ---------------------------------------------------------------------------

// This code converts the variables from table values to document variables

// ---------------------------------------------------------------------------

Let RowCount = NumMax(NoOfRows('CommonVariables'),0)-1;

For i=0 to '$(RowCount)'

Let TempVarName = peek('VariableName',$(i),'CommonVariables');

Let TempVarValue = peek('VariableValue',$(i),'CommonVariables');

Let $(TempVarName) = '$(TempVarValue)';

// -------------------------------------------------------------------------------------------

// You can drop the CommonVariables table if you don't want to use it for display in the app

// -------------------------------------------------------------------------------------------

DROP TABLE CommonVariables;

//------------------------------------------------------------------------

Ths script above works but I do not understand how the variables are stored as Document Variables.

What part of the For Next loop does that? How does QlikView know that it needs to store this as Document Variables?

Re: Can anyone explain how this Variables script works please?

lironbaram — Thu, 26 Nov 2015 14:46:07 GMT

For i=0 to '$(RowCount)'

Let TempVarName = peek('VariableName',$(i),'CommonVariables'); - this row assign the desired name for the variable to

temporary variable

Let TempVarValue = peek('VariableValue',$(i),'CommonVariables'); - this row assign the desired value for the variable to temporary variable

Let $(TempVarName) = '$(TempVarValue)';- this row creates the variable with the parameters from the previous rows

in general a LET\SET Statement means you are creating a variable

Re: Can anyone explain how this Variables script works please?

jblomqvist — Thu, 26 Nov 2015 15:10:03 GMT

Hi Liron,

Thanks. Why do we need a -1 at the end of Let RowCount statement?

Re: Can anyone explain how this Variables script works please?

Peter_Cammaert — Thu, 26 Nov 2015 15:12:24 GMT

Because peeks run from 0 to RowCount-1 (peek() index is 0-based). Otherwise you'll run past the end of the table...

Re: Can anyone explain how this Variables script works please?

lironbaram — Thu, 26 Nov 2015 15:12:58 GMT

because the row count starts from 1 and the

variable i starts from 0

Re: Can anyone explain how this Variables script works please?

Peter_Cammaert — Thu, 26 Nov 2015 15:14:27 GMT

See a similar discussion here: variables evalution confusion?