topic using variables in a sql query in qlikview script in QlikView

using variables in a sql query in qlikview script

Fri, 17 Feb 2012 10:12:13 GMT

Hi, I am using a sql query to load data in a qlikview script.

Has anybody tried using variable instead.

Table1:

SQL Select col1, col2

from sqltable where startdatetime between

DATEADD(mm, DATEDIFF(m,0,GETDATE())-1,0)

AND

DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE())-0,0));

I want to use a variable in the above sql query.

using variables in a sql query in qlikview script

Anonymous — Fri, 17 Feb 2012 10:19:52 GMT

Hope this will be useful for your understanding.

We can use the variable in sql query in script.

ex:

Let LastExecTime = Date(ReloadTime(),'YYYY-MM-DD');

EmployeeDetails:

SQL SELECT

"Emp_name",

"Emp_Sal",

"Audit_Action",

"Audit_Timestamp"

FROM dbo.Employee_Test_Audit

WHERE Audit_Timestamp >= $(LastExecTime);

Here the LastExecTime is an Variable

Regards

Ashok

using variables in a sql query in qlikview script

vijay_iitkgp — Fri, 17 Feb 2012 10:29:12 GMT

Hi you can define the variable by using Let command and use that in sql

Let Date1=Date(Today(),'DD-MM-YYYY');

Select

From A where Postingdate='$(Date1)'

using variables in a sql query in qlikview script

Anonymous — Fri, 17 Feb 2012 11:29:25 GMT

Add single quotes here:

WHERE Audit_Timestamp >= '$(LastExecTime)';

Re: using variables in a sql query in qlikview script

Fri, 17 Feb 2012 14:09:01 GMT

Hi All,

I tried some options but I fail to generate foll. values:

2012-01-01 00:00:00.000 (month starting with 00:00:00)

2012-01-31 23:59:59.000 (month ends with 23:59:59

Does any body has a link to qlikview date formats ? I need to generate datetime as above in qlikview and pass it back to sql query.

using variables in a sql query in qlikview script

Anonymous — Fri, 17 Feb 2012 17:33:40 GMT

There is more than enough info about formats in the QV Help (press F1). See for example timestamp#() function.

In your case, it may be:

timestamp#(YourField, 'YYYY-MM-DD hh:mm:ss[.fff'])

Re: using variables in a sql query in qlikview script

Tue, 06 Nov 2012 17:11:18 GMT

Thanks Ashok!!! His answer was very useful for me.

Re: using variables in a sql query in qlikview script

diwaskarki — Mon, 25 Oct 2021 15:44:13 GMT

I dont think that will work.

I have this in my qvw:

//Find Last Modified Date
Last_Updated_Date:
load max(MODIFIED) as MaxDate
Resident SPT_AUDIT_EVENT;

//Store Last Modified Date to a variable
Let Last_Updated_Date = peek('MaxDate',0,'Last_Updated_Date');

SQL SELECT ID,MODIFIED,ACTION,APPLICATION,INSTANCE,STRING1,STRING3,STRING4,SOURCE,CREATED,TARGET,STRING2,ATTRIBUTES
FROM IDENTITYIQ.SPT_AUDIT_EVENT WHERE INSTANCE='Automated Security Access Removal (ASAR)' Or INSTANCE='RBAC Role Management' Or (ACTION='Remove Group Access' or ACTION='Remove Role Access') AND MODIFIED > $(Last_Updated_Date);

I am getting errors on my sql query because of the variable.