
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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:
[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:
- qlik sap

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
Hi,
Would you mind to give me an example?
Thank you,
Catarina


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
But I dont want to remove the equal fields, I want to load them and rename them.

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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)

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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


- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Report Inappropriate Content
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\.
