Qlik Community

Ask a Question

New to Qlik Sense

If you’re new to Qlik Sense, start with this Discussion Board and get up-to-speed quickly.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
qlikuser22
Creator
Creator

Store sql query in variable

Hi 

I have a select statement written in SQL. 

I want help to store the query result in a variable and use the variable in another SQL query. Please help me if there is any solution. 

Example:

Let abc = select * from table;

SQL select * from table where a =  (§abc); 

Labels (1)
10 Replies
mfchmielowski
Creator II
Creator II

Hi.

SQL query results are tables. You cannot store them as variable, but you can use them.

IT depends on the result the query gives. If you have the query that returns one row you can use peek() function. If there are many rows returned use for each loop.

IF the sql query contains personId column you can write:

Load *;sql

Select personId From persons;

For each vPersonId in FieldValueList('personId')

Load *; sql select personEmail From persons WHERE personId = $(vPersonId) ;

Next vPersonId;

 

qlikuser22
Creator
Creator
Author

Thanks a lot. I have year month date column, I want to concatenate these and find maximum (latest partition) using SQL Hive query in qliksense load. 

Any help for this. 

 

phapalesaurabh
Contributor III
Contributor III

Hello, 

 you can create a SET variable to load the SQL Text in a variable and use it

You can use below code snippet.

Example:

SET abc = 'select * from table';

SQL select * from table where a =  (§abc); 

qlikuser22
Creator
Creator
Author

should we use single quotes to load the select statement. like, SET abc = ' SQL Select * from table a'; else SET abc = sql select * from abc; 

phapalesaurabh
Contributor III
Contributor III

Without quote will also work. 

qlikuser22
Creator
Creator
Author

No it's not fetching any data. I got Lines fetched: 0 Any help. 

phapalesaurabh
Contributor III
Contributor III

Is it possible to share your script log  ?

 Query is getting executed from Qlik Side but I think, there some issue with the sql which you are passing.

 

qlikuser22
Creator
Creator
Author

Sure. 

Let abc = 'SQL select max(concat(cast(`year` as String),'-',lpad(`month`, 2,0),'-',lpad(`day`, 2,0)))
from table';

SQL select * from table where concat(cast(`year` as String),'-',lpad(`month`, 2,0),'-',lpad(`day`, 2,0))) =  §(abc); 

Not able to get any output. 

phapalesaurabh
Contributor III
Contributor III

Can you try again using below statement ?

SET abc = 'Select max(concat(cast(`year` as String),'-',lpad(`month`, 2,0),'-',lpad(`day`, 2,0)))
from table';

SQL select * from table where concat(cast(`year` as String),'-',lpad(`month`, 2,0),'-',lpad(`day`, 2,0))) = (§(abc));