topic SAP Connector: Fetching data from BKPF and BSEG using JOIN in QlikView

SAP Connector: Fetching data from BKPF and BSEG using JOIN

jesper_bagge — Tue, 01 Dec 2009 20:22:10 GMT

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?

SAP Connector: Fetching data from BKPF and BSEG using JOIN

danielrozental — Wed, 02 Dec 2009 06:58:39 GMT

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'));

SAP Connector: Fetching data from BKPF and BSEG using JOIN

jbeierschmitt — Wed, 02 Dec 2009 09:36:51 GMT

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

SAP Connector: Fetching data from BKPF and BSEG using JOIN

jesper_bagge — Wed, 02 Dec 2009 14:21:21 GMT

@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 😉

@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!

SAP Connector: Fetching data from BKPF and BSEG using JOIN

Tue, 08 Dec 2009 05:04:15 GMT

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

SAP Connector: Fetching data from BKPF and BSEG using JOIN

Tue, 08 Dec 2009 05:46:32 GMT

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

SAP Connector: Fetching data from BKPF and BSEG using JOIN

jesper_bagge — Wed, 09 Dec 2009 19:43:40 GMT

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

SAP Connector: Fetching data from BKPF and BSEG using JOIN

Thu, 10 Dec 2009 14:46:08 GMT

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

SAP Connector: Fetching data from BKPF and BSEG using JOIN

pablolabbe — Wed, 20 Oct 2010 16:23:27 GMT

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.

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

jesper_bagge — Thu, 21 Oct 2010 08:44:23 GMT

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

SAP Connector: Fetching data from BKPF and BSEG using JOIN

suniljain — Thu, 21 Oct 2010 11:26:39 GMT

BSEG is cluster table. cluster table donot physical identity at database level. It is logically exist.

so it itself fetch data from multiple table in background processing.

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

jesper_bagge — Thu, 21 Oct 2010 11:46:30 GMT

Yes, I know. This has already been said in this thread.

SAP Connector: Fetching data from BKPF and BSEG using JOIN

suniljain — Fri, 07 Oct 2011 11:06:02 GMT

in qlikview sap connector 5.6 . this task become easy.

thanx to qliktech

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

Tue, 20 Jan 2015 06:37:31 GMT

Hi Sunil,

It will be really helpful if you elaborate your comment

Regards

Kiran Shinde

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

Anonymous — Tue, 14 Jul 2015 15:00:02 GMT

Hi. Can you attach me the qvw you use to extract?

Thanks!