topic Need to restrict data based on date in SQL Statement in QlikView

Need to restrict data based on date in SQL Statement

Thu, 09 Feb 2012 15:18:46 GMT

All,

I need help in restricting data based on date in sql itself. We are using Oracle database. When using the following script, its not restricting data.

load *;

sql select

distinct cust_id,

min(event_time)

from Table1

where

Cust_type = 50

and to_char(event_time, 'yyyy') >= 2011

group by cust_id;

even though my sql has restriction to fetch data after greater than or equal to 2011, still qlikview gives me data before 2011 also.

When i use the same sql statement in oracle, it works perfect.

Need to restrict data based on date in SQL Statement

Thu, 09 Feb 2012 15:27:44 GMT

Hi Neelima,

Two observations:

1. Performance – I will put function on variable rather than the date field, so instead of using

a. To_char(event_time), I will try to_date('2011',’YYYY’)

2. Second, I am bit surprised, that this code is not working as it is working on sql, can you please trying putting ‘2011’ in quotes (I have not tested it)

Regards

Need to restrict data based on date in SQL Statement

Jason_Michaelides — Thu, 09 Feb 2012 15:32:00 GMT

How weird...I've never used Oracle but if I was trying to achieve the same in SQL that would be fine (although it would be a different conversion of event_time to yyyy)

Maybe try using a varibale (I'm guessing here - maybe QV isn't passing the clause to Oracle properly).

LET vWHEREClause = 'to_char(event_time, ' & Chr(39) & 'yyyy' & Chr(39) & ') >= 2011';

load *;

sql select

distinct cust_id,

min(event_time)

from Table1

where

Cust_type = 50

and $(vWHEREClause)

group by cust_id;

Like I say, I'm guessing - but stranger things have happened...

Hope it helps,

Jason

Need to restrict data based on date in SQL Statement

Anonymous — Thu, 09 Feb 2012 16:25:56 GMT

See this post about some aspects of Oracle, hope it helps:

http://community.qlik.com/docs/DOC-1321

Need to restrict data based on date in SQL Statement

Thu, 09 Feb 2012 16:38:36 GMT

Thanks Jason, It worked perfect.

But using variable causes any performance issues?

Need to restrict data based on date in SQL Statement

Jason_Michaelides — Thu, 09 Feb 2012 16:55:19 GMT

Shouldn't do. Using the WHERE clause in the SQL script pushes the load onto the database server which is normally a good thing. And anyway, using the variable shouldn't really make any difference in this case but you get to know QV's little foibles after a while...like I say - your script should work!