Qlik Community

QlikView Connectors

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
catarinabritoid
New Contributor II

Cannot rename fields using Qlik SAP Connector

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

Tags (1)
7 Replies

Re: Cannot rename fields using Qlik SAP Connector

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.

catarinabritoid
New Contributor II

Re: Cannot rename fields using Qlik SAP Connector


Hi,

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

Catarina

Employee
Employee

Re: Cannot rename fields using Qlik SAP Connector

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

catarinabritoid
New Contributor II

Re: Cannot rename fields using Qlik SAP Connector

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

Re: Cannot rename fields using Qlik SAP Connector

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)

catarinabritoid
New Contributor II

Re: Cannot rename fields using Qlik SAP Connector

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:MMSmiley FrustratedS'), 'HH:MMSmiley FrustratedS')                 AS CriadoEmCab_Hora,                       

     Time#(Time(ERZET, 'HH:MMSmiley FrustratedS'), 'HH:MMSmiley FrustratedS')                 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:MMSmiley FrustratedS'), 'HH:MMSmiley FrustratedS')              AS CriadoEmLinha_Hora,       

     Time#(Time(ERZET, 'HH:MMSmiley FrustratedS'), 'HH:MMSmiley FrustratedS')              AS AlteradoEmLinha_Hora;    

SELECT   VBELN  ,  ERDAT  , ERZET   

FROM   VBRP;

Regards,
Catarina

maxloveiii
New Contributor

Re: Cannot rename fields using Qlik SAP Connector

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\.
Community Browser