topic Re: OLE filtering date issue in QlikView

OLE filtering date issue

bkkuah17 — Wed, 25 Nov 2020 16:16:04 GMT

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.

Re: OLE filtering date issue

marcus_sommer — Wed, 31 May 2017 12:35:21 GMT

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

Re: OLE filtering date issue

bkkuah17 — Thu, 01 Jun 2017 01:42:07 GMT

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 ?

Re: OLE filtering date issue

marcus_sommer — Thu, 01 Jun 2017 04:47:38 GMT

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