Qlik Community

Ask a Question

QlikView Connectivity

Discussion board for collaboration related to QlikView Connectors.

Announcements
Join us at the Cloud Data and Analytics Tour! REGISTER TODAY
cancel
Showing results for 
Search instead for 
Did you mean: 
micheledenardi
Specialist II
Specialist II

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
Hakan_Ronningberg

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

View solution in original post

2 Replies
trm
Employee
Employee

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

Hakan_Ronningberg

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

View solution in original post