Create variables with a loop from Excel

[jjordaan]

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

[d_pranskus · Accepted solution]

Hi

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

[#3]

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

[jjordaan]

Darius,

Thanks for your quick help.

It works the way I wanted.

Thanks

[jjordaan]

Niranjan,

Your approach also works.

Thanks for your help as well.