Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

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.
ebg
New Contributor III

SAP Connector: Fetching data from BKPF and BSEG using JOIN

Hi,

I noticed today that the usual JOIN statement doen't seem to work when calling the tables BKPF and BSEG.

The following SQL Statement


SQL Select
BKPF~BELNR,
BKPF~BUDAT,
BKPF~GJAHR,
BKPF~BUKRS,
BSEG~SHKZG,
BSEG~HKONT,
BSEG~BUZEI,
BSEG~DMBTR
from BKPF JOIN BSEG ON BKPF~BELNR = BSEG~BELNR AND BKPF~GJAHR = BSEG~GJAHR AND BKPF~BUKRS = BSEG~BUKRS
;


returns an error.

Im using version 5.0.6526.0 of the SAP Connector.

Using the same logic when fetching data from other tables work fine. It seems to only be when i call the tables BKPF and BSEG.

Any thoughts on why I get an error? Bug perhaps?

1 Solution

Accepted Solutions
Not applicable

SAP Connector: Fetching data from BKPF and BSEG using JOIN

Hi Jesper,

It's not possible, even in Abap code, to make a join on BSEG and BKPF. It's, as you wrote, a cluster table.

It's a joined table from bkpf,bsid,bsad,bsis,bsik,bsak,bset (and other)

For information, GLPCA and GLPCT are controlling table. BSEG,BKPF,BSID,BSAD, and other, are accounting or financial table.

regards

serge

14 Replies
danielrozental
Honored Contributor II

SAP Connector: Fetching data from BKPF and BSEG using JOIN

It's a SAP restriction related to BSEG being a cluster table (not much of an expert, just repeating what I've heard).

You should try something like SQL SELECT BELNR FROM BSEG WHERE (BELNR IN (SELECT BELNR FROM BKPF WHERE CPUDT = '20070819'));

jbeierschmitt
New Contributor III

SAP Connector: Fetching data from BKPF and BSEG using JOIN

No issues that I know of with BKPF/BSEG - try to extract them separately perhaps?

Also, you may find that GLPCA & GLPCT are more useful tables for Finance dashboards...

ebg
New Contributor III

SAP Connector: Fetching data from BKPF and BSEG using JOIN

@Daniel

I'm not sure that the connector can handle such "complex" SQL queries, since I know from earlier experiences that it cannot even handle a simple ALIAS command. Anyhow, your proposed query returns the transactions registered on a single date. Not sure what I could whip up with that since my customer wants a financial application spanning over atleast 3 years Smiley Wink

@jbeierschmitt

Yes, a simple extraction of both tables and the joining of relevant data inside QV has been my previous method, and will have to make do. The GLPCA and GLPCT contains to my knowledge only aggregated data. I always go for the raw stuff, since one of the true strengths of QV is the ability to present data at an aggregated level to the user, but keeping the original transactions for data-drilling.

Cheers!

Not applicable

SAP Connector: Fetching data from BKPF and BSEG using JOIN

Hi Jesper,

It's not possible, even in Abap code, to make a join on BSEG and BKPF. It's, as you wrote, a cluster table.

It's a joined table from bkpf,bsid,bsad,bsis,bsik,bsak,bset (and other)

For information, GLPCA and GLPCT are controlling table. BSEG,BKPF,BSID,BSAD, and other, are accounting or financial table.

regards

serge

Not applicable

SAP Connector: Fetching data from BKPF and BSEG using JOIN

a link if you want the SAP table

or http://www.4shared.com/file/108322076/e2083/sap-tables.html you download the zip file
this is sap-tables.exe it's relation between all sap tables

same thing on PDF

www.abap.es/Descargas/TAB%20-%20Relacion%20de%20las%20tablas%20por%20modulos.PDF

ebg
New Contributor III

SAP Connector: Fetching data from BKPF and BSEG using JOIN

Thank you sdesland. I did not know that BSEG was actually a result of a join of multiple other tables.

Not applicable

SAP Connector: Fetching data from BKPF and BSEG using JOIN

hi jesper

So i said it's a join of table it's to simplify.

BSEG is a cluster/pool table

http://help.sap.com/saphelp_nw04/helpdata/en/cf/21f083446011d189700000e8322d00/content.htm

you can see what is a cluster or a pool table in SAP.For your information,You can't read pool or cluster table directly on the database with an ODBC or other connection SQL from outside SAP. You have to use a sap connector.Other table, transparent table in SAP, no problem.

regards

serge

Luminary
Luminary

SAP Connector: Fetching data from BKPF and BSEG using JOIN

You can´t use subquerys to select data from Cluster Tables like BSEG. I sugest you to create a loop that read data in buckets of 300 DocumentNumber (BELNR) from BKPF in a date range. Then use a the following SELECT to query BSEG:

SELECT BELNR, FIELD2,FIELD3, .....

FROM BSEG

WHERE GJAHR = <YEAR> AND BUKRS = <COMPANY GROUP> AND (BELNR = 30204 OR BELNR = 2049229593 OR BELNR = 0430204 ... OR BELNR = 305030)

You need to put GJAHR and BUKRS in the where clause to use primary index to achieve read performance.

ebg
New Contributor III

SV:Re: SAP Connector: Fetching data from BKPF and BSEG using JOIN

Thank you for your input Pablo.

The user sdesland answered this topic quite some time ago.

An even simpler solution is an incremental load of both BSEG and BKPF where GJAHR and BUKRS serves as key fields. I can do the join later in QlikView.

No loops, no buckets, no worries Wink

Community Browser