Qlik Community

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld online is next week! REGISTER NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
Not applicable

Loading data using greater than date

I'm having trouble loading data using a date where clause. My date field is DT_OF_PYMT. My date format is 7/24/2014  12:00:00 AM. My current load is

ODBC CONNECT TO [ASPEN QV Prod];

SQL SELECT *

FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT";

SPECIALTY:

LOAD LOV_CD AS PHY_SPECIALITY,

    LOV_DESC AS PHY_SPECIALTY_NAME

FROM

[\\ussbfscls06\kvdq645$\Compliance\Open Payments Data Analytics\QlikView\Static Data Sources\SpecialtyCode Values 10-20-2015.xlsx]

(ooxml, embedded labels, table is Sheet1);

I only want to see data for this current year (2016). I've tried multiple formats and get an error each time. Here is what I've tried:

DBC CONNECT TO [ASPEN QV Prod];

SQL SELECT *

FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT"

Where Date(DT_OF_PYMT,'DD-MM-YYYY hh:mm:ss') > '01-01-2016 12:00:00';

SPECIALTY:

LOAD LOV_CD AS PHY_SPECIALITY,

    LOV_DESC AS PHY_SPECIALTY_NAME

FROM

[\\ussbfscls06\kvdq645$\Compliance\Open Payments Data Analytics\QlikView\Static Data Sources\SpecialtyCode Values 10-20-2015.xlsx]

(ooxml, embedded labels, table is Sheet1);

My error Message:

SQL##f - SqlState: 42S02, ErrorCode: 29, ErrorMsg: ERROR:  Function 'DATE(TIMESTAMP, UNKNOWN)' does not exist

  Unable to identify a function that satisfies the given argument types

  You may need to add explicit typecasts

SQL SELECT *

FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT"

Where Date(DT_OF_PYMT,'DD/MM/YYYY hh:mm:ss') > '01/01/2016 12:00:00'

Tried this:

Where Date(Date#(DT_OF_PYMT,'MM/DD/YYYY hh:mm:ss')) > Date(Date#('1/1/2016 00:00:00', 'MM/DD/YYYY hh:mm:ss'));

Got this error:

SQL##f - SqlState: 42000, ErrorCode: 27, ErrorMsg: ERROR:  'SELECT * FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT" Where Date(Date#(DT_OF_PYMT,'MM/DD/YYYY hh:mm:ss')) > Date(Date#('1/1/2016 00:00:00', 'MM/DD/YYYY hh:mm:ss'))'

error                                                                                                         ^ found ")" (at char 102) expecting `IN' or `NOT' or `OVERLAPS' or `Op' or `CONCAT_OP'

SQL SELECT *

FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT"

Where Date(Date#(DT_OF_PYMT,'MM/DD/YYYY hh:mm:ss')) > Date(Date#('1/1/2016 00:00:00', 'MM/DD/YYYY hh:mm:ss'))

1 Solution

Accepted Solutions
Gysbert_Wassenaar

Your SQL Server doesn't speak Qlikview script. So don't use Qlikview function in sql statements that your SQL Server needs to understand. Try this:

DBC CONNECT TO [ASPEN QV Prod];

SQL SELECT *

FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT"

Where DT_OF_PYMT>= '01/01/2016';


talk is cheap, supply exceeds demand

View solution in original post

2 Replies
Gysbert_Wassenaar

Your SQL Server doesn't speak Qlikview script. So don't use Qlikview function in sql statements that your SQL Server needs to understand. Try this:

DBC CONNECT TO [ASPEN QV Prod];

SQL SELECT *

FROM "SPD_RPT".ADMIN."GNRL_PYMT_DTL_FACT"

Where DT_OF_PYMT>= '01/01/2016';


talk is cheap, supply exceeds demand
marcus_sommer
MVP & Luminary
MVP & Luminary

Your used date or date# functions are qlikview-functions and couldn't be used within the sql-statement. If your load is rather small you could transfer these filtering to the qlikview side maybe within a resident load or more commonly used a Preceding Load.

In your case might it be easier to use:

...

Where year(DT_OF_PYMT) >= 2016;

then I think year would be available within your database and your used odbc/oledb-driver.

- Marcus