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.
avastani
Contributor II

SAP Join Syntax

I am using the following syntax and it doesn't work

Test:

LOAD VBRK~VBELN, VBRP~WERKS;

Sql Select VBRK~VBELN VBRP~WERKS

FROM VBRK

JOIN VBRP ON VBRK~VBELN = VBRP~VBELN

WHERE VBRK~FKDAT = '2012-04-01';

If I remove the Load statement it works fine. What am I missing here? How can we use the Preceding Load or is it that Preceding Loads are not possible in a join scenario when using SAP Connector.

8 Replies
Sokkorn
Honored Contributor

Re: SAP Join Syntax

Hi amirvas,

amirvas wrote:


If I remove the Load statement it works fine. What am I missing here? How can we use the Preceding Load or is it that Preceding Loads are not possible in a join scenario when using SAP Connector.

Now let try

LOAD VBRK~VBELN As F1, VBRP~WERKS As F2;

Sql Select VBRK~VBELN VBRP~WERKS

FROM VBRK

JOIN VBRP ON VBRK~VBELN = VBRP~VBELN

WHERE VBRK~FKDAT = '2012-04-01';

Regards,

Sokkorn

avastani
Contributor II

Re: SAP Join Syntax

Never mind. In the load statement I need to remove the table qualifier and it works fine.

Sokkorn
Honored Contributor

Re: SAP Join Syntax

Good to hear that.

avastani
Contributor II

Re: SAP Join Syntax

the odd part to think about is how does SAP Connector know which field is from which table if duplicate fieldnames like NETWR, VBELN etc are involved.

suniljain
Honored Contributor

Re: SAP Join Syntax

DIRECTORY;

[VBAP - Order Items]:

LOAD

VBELN & '/'& POSNR as [%VBELN/POSNR],

[MEINS] as [Base Unit_MEINS],

[MATKL] as [Material Group_MATKL],

[WERKS] as [Plant_WERKS],

[MATNR] as [Material_MATNR],

[SPART] as [Division_SPART],

[KPEIN] as [Pricing unit_KPEIN],

[KMEIN] as [Unit of measure_KMEIN],

[ERNAM] as [Created by_ERNAM],

[ERDAT] as [Created on_ERDAT],

[AEDAT] as [Changed on_AEDAT],

[POSNV] as [Item_POSNV],

[VSTEL] as [Shipping Point_VSTEL],

[VBELN] as [Sales Document_VBELN],

[POSNR] as [Item_POSNR],

[NETWR] as [Net value_NETWR];

SQL SELECT MEINS MATKL WERKS MATNR SPART KPEIN KMEIN ERNAM ERDAT AEDAT POSNV VSTEL VBELN NETWR POSNR

FROM VBAP

WHERE VBELN IN (

SELECT VBELN FROM VBAK // Order Header

WHERE ERDAT > '$(vLastReload)'

OR AEDAT > '$(vLastReload)'

);

CONCATENATE ([VBAP - Order Items])

LOAD *

FROM VBAP.qvd (qvd)

WHERE not exists ([%VBELN/POSNR]);

avastani
Contributor II

Re: SAP Join Syntax

Hi,

Thanks for the reply but that's not what I am looking for. I want to do a join between VBRK and VBRP and also being in both NETWR from VBRK and VBRP. One is a header amount, the other is item amount.

Hope this helps.

suniljain
Honored Contributor

Re: SAP Join Syntax

You have to use qualify and unqualify for that.

avastani
Contributor II

Re: SAP Join Syntax

So essentially the connector doesn't support aliasing and obtaining both fields in SQL but rather in LOAD statements once in QV side.

-afv.

Tel: 646.773.7936

Community Browser