iterate over a list of variables and execute load script

[devarshigoswami]

Hi!

Thanks for taking the time to read this. I'm quite new to this tool and am loving it 🙂 

I am using QlikView server and I have a load script that extracts data using SQL.

SQL select * from table

where parameter1 =  'a'

and parameter2  =  '1'

;

I want to have 2 lists like so:

lis1 = ['a', 'b', 'c']

and lis2 = ['1', '2', '3']

such that I can create a loop statement on these variables to change the values for parameter1 and parameter2.

like so:

iteration 1: 

SQL select * from table

where parameter1 =  lis1[1]

and parameter2  =  lis2[1]

;

iteration 2: 

SQL select * from table

where parameter1 =  lis1[2]

and parameter2  =  lis2[2]

;

and so on....

is this feature possible in qlik? if so , how can I implement it? 

[marcus_sommer · Accepted solution]

You may use  a simple loop with variables like:

set lis1 = a, b, c; set lis2 = 1, 2, 3;

for i = 1 to substringcount('$(lis1)', ',') + 1
   let f1 = subfield('$(lis1)', ',', $(i)); let f2 = subfield('$(lis2)', ',', $(i));
   t: sql select * from table where par1 = '$(f1)' and par2 = $(f2);
next

[devarshigoswami]

Thanks for your answer, Marcus!

What if I wanted to concatenate the different tables created in each loop? Or will it be done by qlik since the table name is going to be the same? 

Is there a possibility to do that as well? 

[marcus_sommer]

If these tables have the same data-structure they would be auto-concatenated but it's best practice to make everything explicit and to specify the wanted statement by including the table-name.