Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
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

Re: Loading data using greater than date

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
2 Replies

Re: Loading data using greater than date

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

Re: Loading data using greater than date

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

Community Browser