topic Using between operator in for data filtering in QlikView

Using between operator in for data filtering

Wed, 25 Nov 2020 16:16:04 GMT

Hi all,

am a new bie to qlik community.

kindly apologize , if my doubt is basic.

i have a table having records as follows

col1 col2

1 2013-09-10 05:29:34.383

2 2013-09-10 05:30:45.980

3 2013-12-18 04:46:55.723

4 2013-12-18 04:47:29.360

5 2014-01-09 04:49:00.267

6 2014-02-25 06:13:01.063

now i want to filter the data based on the user input .

for that i defined 2 input parameters as follows

LET vinputstdate =input('input Startdate','warning');

LET vinputseddate =input('input end date','warning');

later i assigned it to another variable for date conversion

Let startTime = Date(vinputstdate,'YYYY-MM-DD');

let endtime = Date(vinputseddate,'YYYY-MM-DD');

and i queried like

SELECT *

FROM databasetable

WHERE col2>= $(startTime) AND col2<= $(endtime);

but no data fetching for me , i given input as startdate: 2013-09-10 05:29:34.383 and enddate : 2014-02-25 06:13:01.063

but no data was fetching.

i tried WHERE col2>= '$(startTime)' AND col2<= '$(endtime)'; also after referring our community website. no result.

can any one help me on this. hereby i attach my qvw for reference.

thanks in Advance...

With Regards

Deepak Subramoniam

Re: Using between operator in for data filtering

krishnacbe — Tue, 28 Jun 2016 15:25:30 GMT

Check the Data formatting is correct with DB table. if then then you need to format the variable.

The variable value is string and if you compare the string with date no result will be generated. Use convert functions and try.

Re: Using between operator in for data filtering

sunny_talwar — Tue, 28 Jun 2016 20:05:07 GMT

Can you try this:

ODBC CONNECT32 TO server;

LET vinputstdate =input('input Startdate','warning');

LET vinputseddate =input('input end date','warning');

LET startTime = Chr(39) & Date(vinputstdate,'DD-MMM-YYYY') & Chr(39);

LET endtime = Chr(39) & Date(vinputseddate,'DD-MMM-YYYY') & Chr(39);

SQL SELECT *

FROM Databasetable

WHERE col2 >= $(startTime) AND col2 <= $(endtime);

Re: Using between operator in for data filtering

Wed, 29 Jun 2016 04:26:34 GMT

Sunny, thanks for your help, its working fine now.

may i know the issue was vinputstdate or starttime , because , i hope doen a conversion here.

please clarify.

your reply will help me lot in future.

thanks for spending your valuable time towards my query.

Thanks in Advance...

With Regards
Deepak Subramoniam