topic Re: Dynamic SQL Statement in QlikView

Dynamic SQL Statement

haymarketpaul — Tue, 12 Jun 2012 14:42:49 GMT

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.

Re: Dynamic SQL Statement

RedSky001 — Tue, 12 Jun 2012 16:51:24 GMT

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

;

Mark

Re: Dynamic SQL Statement

haymarketpaul — Wed, 13 Jun 2012 14:01:28 GMT

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

1976,1826,2004,1673

then i get nothing back?

Re: Dynamic SQL Statement

RedSky001 — Wed, 13 Jun 2012 15:06:01 GMT

Try dropping the quotes

so change

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

WHERE U.cmpid IN ( $(vQueryParamter) )

Re: Dynamic SQL Statement

haymarketpaul — Wed, 13 Jun 2012 15:14:35 GMT

Perfect - Thanks mate

Re: Dynamic SQL Statement

vsabbise — Mon, 08 Oct 2018 21:26:12 GMT

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

Re: Dynamic SQL Statement

haymarketpaul — Tue, 09 Oct 2018 09:48:52 GMT

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.

Re: Dynamic SQL Statement

vsabbise — Tue, 09 Oct 2018 13:29:05 GMT

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