using variables in a sql query in qlikview script

[#1]

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.

[Anonymous]

Hi

     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

[vijay_iitkgp]

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)'

[Anonymous]

Add single quotes here:

WHERE Audit_Timestamp >= '$(LastExecTime)';

[#5]

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.

[Anonymous]

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'])

[#7]

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

[diwaskarki]

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.