Qlik Community

QlikView Creating Analytics

Discussion Board for collaboration related to Creating Analytics for QlikView.

haymarketpaul
Contributor III

Dynamic SQL Statement

Hi All

QV11 SR1, SQL Server 2008

I have a SQL Server select statement like the one below in QV...

SELECT cmpname,

       Count (DISTINCT U.useremail) AS [OptIn Email]

FROM   tbl_users U

WHERE  U.cmpid IN ( 1838, 6, 1, 3, 1956, 4 )

       AND U.useroptinhbplemails = 1

GROUP  BY cmpname

ORDER BY cmpname

;

i would like to pass in various numbers to part of the WHERE clause (part in bold above)

i don't have the ability to create stored procedures on the server.

Can i dynamically construct the SQL Statement within QV so that i could read in the numbers i want from a spreadsheet or variable and then execute the SQL ?  I don't want to write the same query loads of times with different numbers in that part of the WHERE clause if i can help it.

Any ideas appreciated.

Tags (1)
1 Solution

Accepted Solutions
RedSky001
Contributor III

Re: Dynamic SQL Statement

Try dropping the quotes

so change

WHERE  U.cmpid IN ( '$(vQueryParamter)' )

to

WHERE  U.cmpid IN ( $(vQueryParamter) )

7 Replies
RedSky001
Contributor III

Re: Dynamic SQL Statement

Paul,

Something like this:

Temp:

LOAD * INLINE [

    Query, Numbers

    1, 1

    1,2

    2,4

    2,44

];

Numbers:

load Query

,Concat(Numbers,',') as QueryString

Resident Temp

Group by Query;

Drop table Temp;

Counts:

LOAD Count(Query) as Y

Resident Numbers;

LET y = Peek('Y', 0) - 1;

DROP Table Counts;

// for each Query

for k = 0 to $(y)

                    LET vQueryParamter = PEEK('QueryString', $(k),'Numbers');

 

                              TEMP:

                              SQL SELECT cmpname,

                                     Count (DISTINCT U.useremail) AS [OptIn Email]

                              FROM   tbl_users U

                              WHERE  U.cmpid IN ( '$(vQueryParamter)' )

                                     AND U.useroptinhbplemails = 1

                              GROUP  BY cmpname

                              ORDER BY cmpname

                              ;

 

next

Mark

haymarketpaul
Contributor III

Re: Dynamic SQL Statement

Thanks Mark

That almost works nicely.

If   vQueryParameter = only one number

then it works nicely, but if it equals multiple numbers (which is usually the case)

e.g.

1972,3

or

1976,1826,2004,1673

then i get nothing back?

RedSky001
Contributor III

Re: Dynamic SQL Statement

Try dropping the quotes

so change

WHERE  U.cmpid IN ( '$(vQueryParamter)' )

to

WHERE  U.cmpid IN ( $(vQueryParamter) )

haymarketpaul
Contributor III

Re: Dynamic SQL Statement

Perfect - Thanks mate

vsabbise
New Contributor III

Re: Dynamic SQL Statement

May I know how do I do this in Qlik Sense ? Especially would like to know how to use IN operator in Qlik Sense.

haymarketpaul
Contributor III

Re: Dynamic SQL Statement

The IN operator in my question above is actually part of SQL rather than Qlik view/sense syntax - if that is the IN operator you are referring to then you can happily use SQL in Qlikview and Qliksense in the same way including the IN operator.  Have you got an example.

vsabbise
New Contributor III

Re: Dynamic SQL Statement

Thanks for your response. So can I use the as is sql script in the expression window ? Here is my detailed question. How to use 'IN' operator with 'variable' in Qlik Sense