Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
jjordaan
Partner - Specialist
Partner - Specialist

Create variables with a loop from Excel

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

1 Solution

Accepted Solutions
d_pranskus
Partner - Creator III
Partner - Creator III

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

View solution in original post

4 Replies
d_pranskus
Partner - Creator III
Partner - Creator III

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

Not applicable

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
Partner - Specialist
Partner - Specialist
Author

Darius,

Thanks for your quick help.

It works the way I wanted.

Thanks

jjordaan
Partner - Specialist
Partner - Specialist
Author

Niranjan,

Your approach also works.

Thanks for your help as well.