topic iterate over a list of variables and execute load script in App Development

iterate over a list of variables and execute load script

devarshigoswami — Fri, 20 Jan 2023 07:27:18 GMT

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?

Re: iterate over a list of variables and execute load script

marcus_sommer — Fri, 20 Jan 2023 11:01:18 GMT

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

Re: iterate over a list of variables and execute load script

devarshigoswami — Mon, 23 Jan 2023 05:20:13 GMT

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?

Re: iterate over a list of variables and execute load script

marcus_sommer — Mon, 23 Jan 2023 06:34:17 GMT

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.