Cannot rename fields using Qlik SAP Connector

[Anonymous]

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:

[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

[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.

[Anonymous]

Hi,

Would you mind to give me an example?
Thank you,

Catarina

[trm]

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

[Anonymous]

But I dont want to remove the equal fields, I want to load them and rename them.

[Peter_Cammaert]

I don't have a SAP Connector om my laptop, unfortunately. But if you installed the SAP Connector on a server, you can find the ScriptBuilder in C:\ProgramData\QlikTech\Custom Data\QvSAPConnector\ScriptBuilder. Open and configure the connections in ReloadSAPDD.qvd, save and reload. This document will load the SAP metadata and store it in a couple of local QVDs.

Then open the ScriptBuilder.qvw document and reload. If all goes well, navigate to tab "Generate Script" to create a correct script for any tzble you whish to extract. Add JOINs and WHERE conditions after the transfer to your document or script file.

Info on OpenSQL can be found here: SAP Library - ABAP Programming (BC-ABA)

[Anonymous]

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

[maxloveiii]

Hi Catarina
I recommend you use ScriptBuilder.qvw, that is used to find tables to download from the SAP system and to generate the script code. Find out this path.

C:\ProgramData\QlikTech\Custom Data\QvSAPConnector\ScriptBuilder\.