Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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?
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
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
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?
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.