6 Replies Latest reply: May 6, 2017 7:18 AM by Catarina Brito RSS

    Cannot rename fields using Qlik SAP Connector

    Catarina Brito

      Hi People,

      I'm loading data from SAP using SAP Qlik Connector with option "Preceding Load".
      I use this option to be able to use alias. However, when I reload the data it fails, showing the following message:


      The reload script is:Erro_Connector.JPG


      [SALES_SAP]:



      LOAD
      VBRK.VBELN, // Doc.fatur.
      VBRK.ERDAT, // Em
      VBRK.FKDAT AS Date, // Dt.fatur.
      VBRK.BUKRS, // Empr.
      VBRK.VKORG, // Org.vendas
      VBRK.VBTYP, // Ctg.doc.SD
      VBRK.AEDAT AS AEDAT_VBRK, // Mod.
      VBRK.ERZET AS ERZET_VBRK, // Hora

      VBRP.POSNR, // Item
      VBRP.MATNR, // Material
      VBRP.WERKS, // Centro
      VBRP.FKIMG, // Qtd.faturd
      VBRP.MWSBP, // Imposto
      VBRP.CMPRE, // Preço
      VBRP.WAVWR, // ValInterno
      VBRP.NETWR, // Val.líq.
      VBRP.ERDAT AS ERDAT_VBRP, // Em
      VBRP.ERZET AS ERZET_VBRP; // Hora

      SELECT
      VBRK.VBELN, // Doc.fatur.
      VBRK.ERDAT, // Em
      VBRK.FKDAT, // Dt.fatur.
      VBRK.BUKRS, // Empr.
      VBRK.VKORG, // Org.vendas
      VBRK.VBTYP, // Ctg.doc.SD
      VBRK.AEDAT, // Mod.
      VBRK.ERZET, // Hora

      VBRP.POSNR, // Item
      VBRP.MATNR, // Material
      VBRP.WERKS, // Centro
      VBRP.FKIMG, // Qtd.faturd
      VBRP.MWSBP, // Imposto
      VBRP.CMPRE, // Preço
      VBRP.WAVWR, // ValInterno
      VBRP.NETWR // Val.líq.
      VBRP.ERDAT, // Em
      VBRP.ERZET // Hora


      FROM VBRK INNER JOIN VBRP ON VBRK.VBELN = VBRP.VBELN

      WHERE VBRK.BUKRS LIKE 'CF00'
      AND VBRK.VKORG LIKE 'CF01';


      STORE [SALES_SAP] into SourceFiles\FinalQVDs_SAP\SALES_SAP.qvd(qvd);


      *****************************************************************************************************************


      Team, do you any idea I can I do it?

      Thank you,


      Catarina

        • Re: Cannot rename fields using Qlik SAP Connector
          Peter Cammaert

          You are not using the SAP Connector, you seem to be querying a SAP SQL DB directly.

           

          The SAP Connector uses a SQL version called OpenSQL to query the SAP SQL interface. One of the distinctive features of OpenSQL is that you cannot separate column names by comma's. Which you are doing in your example.

           

          To correct your basic SELECT, you can use the ScriptBuilder to generate an appropriate OpenSQL query. The preceding LOAD will be included (if you want) and can be used to perform any rename you'ld like.

          • Re: Cannot rename fields using Qlik SAP Connector
            Thomas Örnmarker

            Hi Catarina,

            You should try using ~ as a table-field separator instead of the .

             

            This query works for me:

            SELECT

            VBRK~VBELN

            VBRK~ERDAT

            VBRK~FKDAT

            VBRK~BUKRS

            VBRK~VKORG

            VBRK~VBTYP

            VBRK~AEDAT

            VBRK~ERZET

             

             

            VBRP~POSNR

            VBRP~MATNR

            VBRP~WERKS

            VBRP~FKIMG

            VBRP~MWSBP

            VBRP~CMPRE

            VBRP~WAVWR

            VBRP~NETWR

             

            FROM VBRK INNER JOIN VBRP ON VBRK~VBELN = VBRP~VBELN

             

            WHERE VBRK~BUKRS LIKE 'CF00'

            AND VBRK~VKORG LIKE 'CF01';

             

            Note that I also removed the last two fields as their names were the same previously selected from VBRK

             

            Regards,

            // Thomas Örnmarker

            SAP Connectors Developer @ Qlik

            • Re: Cannot rename fields using Qlik SAP Connector
              Catarina Brito

              Hi People,

               

              I as able to resolve my problem.

              Unfortunatly Qlik SAP connector only allow you to load and rename fields from only one table.

              The solutions (and in the end will be the same as I wanted to do) is:

               

              We load the first table, for example VBRK (header sales) and rename de fields as you wish.

              Then do a LEFT JOIN with VBRP (sales lines) rename de fields also as you want and make sure the the field which will make the connection between this two table is equal (has the same name). Then, Will be created a table Sales_SAP as you want in only one QVD.

               

              IE:

               

              [SALES_SAP] :

              LOAD

                  VBELN                                                                            AS NumeroDoc,                           

                   Date#(Date(ERDAT, 'DD/MM/YYYY'), 'DD/MM/YYYY')        AS CriadoEmCab,                        

                   Date#(Date(AEDAT, 'DD/MM/YYYY'), 'DD/MM/YYYY')        AS AlteradoEmCab,                         

                   Time#(Time(ERZET, 'HH:MM:SS'), 'HH:MM:SS')                 AS CriadoEmCab_Hora,                       

                   Time#(Time(ERZET, 'HH:MM:SS'), 'HH:MM:SS')                 AS AlteradoEmCab_Hora;                     SELECT  VBELN ,  ERDAT ,   AEDAT ,  ERZET  

              FROM  VBRK WHERE BUKRS = 'CF00' AND VKORG = 'CF01';

              STORE  [SALES_SAP]  into SourceFiles\FinalQVDs_SAP\SALES_SAP.qvd(qvd);

               

               

              LEFT JOIN (SALES_SAP)

              LOAD 

                   VBELN                                                                        AS NumeroDoc,   

                   Date#(Date(ERDAT, 'DD/MM/YYYY'), 'DD/MM/YYYY')     AS CriadoEmLinha,           

                   Date#(Date(ERDAT, 'DD/MM/YYYY'), 'DD/MM/YYYY')     AS AlteradoEmLinha,           

                   Time#(Time(ERZET, 'HH:MM:SS'), 'HH:MM:SS')              AS CriadoEmLinha_Hora,       

                   Time#(Time(ERZET, 'HH:MM:SS'), 'HH:MM:SS')              AS AlteradoEmLinha_Hora;    

              SELECT   VBELN  ,  ERDAT  , ERZET   

              FROM   VBRP;

               

              Regards,
              Catarina