Skip to main content

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Welcome to Qlik Community! Check out our new navigation! FIND OUT MORE
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

using variables in a sql query in qlikview script

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.

7 Replies
chandashok
Creator III
Creator III

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
Partner - Specialist
Partner - Specialist

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

mov
Employee
Employee

Add single quotes here:

WHERE Audit_Timestamp >= '$(LastExecTime)';

Not applicable
Author

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.

mov
Employee
Employee

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

Not applicable
Author

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

diwaskarki
Creator II
Creator II

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.