topic Re: Create variables with a loop from Excel in QlikView

Create variables with a loop from Excel

jjordaan — Mon, 18 Jun 2012 08:23:48 GMT

Hi All

We have an Excel with a seperate tab for all the KPI's with the expressions we use in the QV dashboard.
I'm trying to create a loop so all variables will be automaticly be created.

I'm trying to do something like this:

KPI:
LOAD
      KPICode,
      KPI                   AS KPI_Desc,
      Expression       AS KPI_Expression
FROM
[..\Nav_Aansturing\Qlikview control file.xlsx]
(ooxml, embedded labels, table is [KPI's])
Where not IsNull(KPI);

LET vKPI = NoOfRows('KPI');

For i = 1 to $(vKPI)

LET $(i)_vKPICode = FieldValue('KPI_Expression',$(i));

NEXT i;

The thing is that I can not make it work that the variable name is created with a variabele name.
I Have added the Excel file with variabele I want to create.

Thanks for the help

Re: Create variables with a loop from Excel

d_pranskus — Mon, 18 Jun 2012 08:30:48 GMT

You can use the following approach

FOR var_i = 1 TO NOOFROWS('KPI')

l_var = PEEK('KPICode, var_i - 1, 'KPI');

l_val = PEEK('KPI_Expression', var_i - 1, 'KPI');

SET $(l_var) = $(l_val);

NEXT var_i;

Cheers

Darius

Re: Create variables with a loop from Excel

Mon, 18 Jun 2012 08:46:21 GMT

Hi,

this script will help you...

LET

vKPI = NoOfRows('KPI');

For i = 1 to $(vKPI)

Let Variable = FieldValue('KPICode',$(i));

LET $(Variable) = FieldValue('KPI_Expression',$(i));

NEXT i;

Thanks,

niranjan M

Re: Create variables with a loop from Excel

jjordaan — Mon, 18 Jun 2012 08:46:37 GMT

Darius,

Thanks for your quick help.

It works the way I wanted.

Thanks

Re: Create variables with a loop from Excel

jjordaan — Mon, 18 Jun 2012 08:50:25 GMT

Niranjan,

Your approach also works.

Thanks for your help as well.