3 Replies Latest reply: Oct 15, 2014 6:07 AM by Yaniv Feldman RSS

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

    Yoca Yoca

      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.

        • Re: Filter by YearMonth in a date field (YearMonthDay) in BW
          Yaniv Feldman

          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

            • Re: Filter by YearMonth in a date field (YearMonthDay) in BW
              Yoca Yoca

              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.

                • Re: Filter by YearMonth in a date field (YearMonthDay) in BW
                  Yaniv Feldman

                  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.