Filter by YearMonth in a date field (YearMonthDay) in BW

[Anonymous]

Hi, we connect to SAP BW with SAP SQL Connector

We have the field “/BIC/Z99CDAOR” in a ODS in SAP BW. It contains dates (example 01.05.2014). We have to do an incremental load and I have to filter by YYYYMM.

We want to transform the field “/BIC/Z99CDAOR” to the YYYYMM format in order to do the filter. How can we do it?

F_ORDER_DATA:

SELECT *

FROM /BIC/AZ99OS00700

where /BIC/Z99CDAOR = '201405'; //of course it does not match!!

Thanks in advance.

[Anonymous]

Hi Yoca

I am not sure that you can use string/text/date functions in the Where clause in SAP SQL statement.

However, I can suggest to use date ranges. For example of you wanna extract 201405, just extract every date between 20140501 and 20140601:

Try this code, it should work (works for me...):

F_ORDER_DATA:

SELECT *

FROM /BIC/AZ99OS00700

where /BIC/Z99CDAOR GE '20140501'

and /BIC/Z99CDAOR LT '20140601'

BTW:

GE means in SAP ABAP 'Greater or Equal' :      >=

LT means 'Less Than' :    <

It shouldn't be difficult to maintain variables for these filters

Yaniv

[Anonymous]

Hi,

Yaniv, thank for your answer!!

3 more questions 🙂

1) We have the code to extract by YYYYMM and we cannot use it to extract by dates YYYYMMDD. Do you know another workaround to do the SQL comparing with a YYYYMM parameter?

2) Do you know what is the sintaxis for BETWEEN. We are using the SAP SQL Connector and when we use the BT sintax (where /BIC/Z99CFEPE BT ‘20131201’ ‘20131212’) we receive the error “BT: invalid relational operator":

3) Do you have a manual with the full SQL sintaxis for SAP SQL Connector? We downloaded the manual from the Qlik site: “QlikView Connector for Use with  SAP NetWeaver” but in that manual I am afraid that only explains the SQL sintaxis for SAP DSO Connector.

Thanks in advance.

[Anonymous · Accepted solution]

1. Add the following variables to your script:

     let vCurrMonth = date(today(),'YYYYMM')&'01' ; //Current Month YYYYMM01

     let vNextMonth = date(addmonths(today(),1),'YYYYMM')&'01' ; /Next Month YYYYMM01

F_ORDER_DATA:

SELECT *

FROM /BIC/AZ99OS00700

where /BIC/Z99CDAOR GE '$(vCurrMonth)'

and /BIC/Z99CDAOR LT '$(vNextMonth)' ;

It will work.

2+3.  I am not familiarized with any documentation about allowed syntax in the QV SQL Connector. Generally, it is quite similar the ABAP SQL syntax. You can google: 'ABAP SQL Syntax' in order to learn more details.