Qlik Community

QlikView Connectors

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.
micheledenardi
Valued Contributor

Where clause with Date comparison in SAP Connector

Hi expert,

i'm using Qlik Sap connector to extract some data and want to extract all the record that AEDAT is major than yesterday.

I have already tried to this solution

SQL SELECT *

FROM  TABLE

WHERE AEDAT >= 20171001;


and also


SQL SELECT *

FROM  TABLE

WHERE AEDAT >= '20171001';


and also


SQL SELECT *

FROM  TABLE

WHERE AEDAT >= '01/10/2017';


and also


SQL SELECT *

FROM  TABLE

WHERE AEDAT >= 01/10/2017;


but it doesn't work because AEDAT is stored with DD/MM/YYYY format and i don't know how to compare AEDAT with yesterday date in order to obtain all record with AEDAT >= yesterday.

Any ides ?


1 Solution

Accepted Solutions
Employee
Employee

Re: Where clause with Date comparison in SAP Connector

Hi Michele,

Is the field AEDAT of data type DATS? If so it should be written like:

SQL SELECT * FROM  TABLE WHERE AEDAT >= '20171001';


SAP stores dates like '20171001' so the user setting for displaying dates should not matter.


You can test the statement in the transaction n/QTQVC/SQL in SAP. Online help:


SAP SQL Connector user configuration ‒ Qlik Connectors

Regards,

Hakan

2 Replies
Employee
Employee

Re: Where clause with Date comparison in SAP Connector

Hi Michele,

The following works for me:

// SAP uses the format 20171001 for dates

// Subtracting 1 is the same as subtracting 1 day, since there's no yesterday() function in QV.

LET date = Date(Today(1) - 1 , 'YYYYMMDD');

[VBAK]:

SELECT AUDAT // Doc. Date

FROM VBAK

WHERE AUDAT >=  '$(date)';

// Make sure to quote the date

Regards,

// Thomas Örnmarker

SAP Connectors Developer

Employee
Employee

Re: Where clause with Date comparison in SAP Connector

Hi Michele,

Is the field AEDAT of data type DATS? If so it should be written like:

SQL SELECT * FROM  TABLE WHERE AEDAT >= '20171001';


SAP stores dates like '20171001' so the user setting for displaying dates should not matter.


You can test the statement in the transaction n/QTQVC/SQL in SAP. Online help:


SAP SQL Connector user configuration ‒ Qlik Connectors

Regards,

Hakan

Community Browser