Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Now Live: Qlik Sense SaaS Simplified Authoring – Analytics Creation for Everyone: READ DETAILS
cancel
Showing results for 
Search instead for 
Did you mean: 
bkkuah17
Contributor II
Contributor II

OLE filtering date issue

Hi,

Currently I having problem retrieving data by using OLE connection from MSSQL. Below is the script used.

Output:

SQL

  select

  Top 100

       *

  from

       SourceTable

  where

  DateValue >= convert(DATETIME,'2016-09-21 00:00:03',206)

  and DateValue < convert(DATETIME,'2016-09-21 10:25:03',206)

;

However when I try the same code in the native MSSQL client. it does work.

Further troubleshooting, Qlikview manages to display data without using date in filtering criteria.

Output:

SQL

  select

  Top 100

       *

  from

       SourceTable

  where

          Column1 = 'A'

;


Is there a way to retrieve data from MSSQL within a specific date range ? I had try several different style but with no luck, like,

  • datediff( day ,DateValue ,'2016-09-21 00:00:03' ) >= 0  and datediff( day ,DateValue ,'2016-09-21 10:25:03' ) <0
  • DateValue between '2016-09-21 00:00:03' and '2016-09-23 10:25:03'
  • Cast(  replace( convert( varchar(30) ,DateValue ,112 ) +convert( varchar(30) ,DateValue ,108 ),':','') as bigint )  >= 20160921000003 and Cast(  replace( convert( varchar(30) ,DateValue ,112 ) +convert( varchar(30) ,DateValue ,108 ),':','') as bigint )  < 20160921102503

But with no success. Is there a way to achieve this ? or is it a limitation Qlikivew & OLEDB ?

Thanks.

1 Solution

Accepted Solutions
marcus_sommer
MVP & Luminary
MVP & Luminary

Does any load work without a where-statement? I think it's rather an issue with your used oledb-driver - try it with a different one or with an odbc-driver.

- Marcus

View solution in original post

3 Replies
marcus_sommer
MVP & Luminary
MVP & Luminary

Does any load work without a where-statement? I think it's rather an issue with your used oledb-driver - try it with a different one or with an odbc-driver.

- Marcus

bkkuah17
Contributor II
Contributor II
Author

ODBC seem to be working, looks like OLE to be the issue.

Qlikview server which need to link with various DB via OLE will hit problems if OLE drivers not install for each db it uses ?

marcus_sommer
MVP & Luminary
MVP & Luminary

In each case you will need the right db-drivers and if you want to access several db's you might need a whole bunch of different drivers. As long as you don't need to pull constantly huge datasets and/or you had only quite small time-frames for it go for odbc-drivers as they are simpler and more robust and many transformations could be done within a Preceding Load so that it not needs mandatory complex sql-statements.

- Marcus