topic Re: Multiple table join but with field switch in the middle. in App Development

Multiple table join but with field switch in the middle.

Daryn — Tue, 21 Nov 2023 09:48:56 GMT

Hi,

I tried to get this working, with some joins using created fields = shared1 and shared2.

Shared1 was for LIKP = VBELN, LIPS = VBELN and VBFA = VBELV

Shared2 was for VBFA = VBELN & VBAK = VBELN

VBFA is the document flow table (and VBELV is the document number matching VBELN (preceding document) in LIKP & LIPS, and it's VBELN matches the VBELN (succeeding document) in VBAK.

However I don't appear to have got it correct as my final data is not complete rows contaning values from all fields.

What I want to do is;

Please note again the switch in VBFA between VBELV and VBELN (the document numbers in VBAK = VBELN do not match VBELN in LIPK or LIPS).

All 4 tables are separate QVD sources.

Any help/script with the best way to achieve this is appreciated.

Thanks Daryn

LIKP

Example data

LIPS

Example data

VBFA

Example data

VBAK

Example data

Final table

ERDAT

01.01.2023

ERDAT

01.01.2023

ERDAT

01.01.2023

ERDAT

01.01.2023

ERDAT

01.01.2023

VBELN

89898989

VBELN

89898989

VBELV

89898989

VBELN

89898989

VBELN

12121212

VBELN

12121212

VBELV

89898989

LPRIO

123

MATNR

1234

NETWR

999.99

VBELN

12121212

LFART

456

PRODH

6789

LPRIO

123

SDABW

789

LFART

456

SDABW

789

MATNR

1234

PRODH

6789

NETWR

999.99

Outer join - keep all records from both - one table with all fields

Document flow table to allow join to VBAK

Table that holds the values

New table holding all fields, producing rows containing each fields value

Re: Multiple table join but with field switch in the middle.

panosalexand — Wed, 22 Nov 2023 22:27:44 GMT

Hi Daryn,
Tomorrow you will have the script.

Re: Multiple table join but with field switch in the middle.

Aasir — Thu, 23 Nov 2023 01:07:12 GMT

Try this

// Load LIKP data
LIKP:
LOAD
ERDAT,
VBELN,
LPRIO,
LFART,
SDABW
FROM LIKP.qvd;

// Load LIPS data
LIPS:
LOAD
ERDAT,
VBELN,
MATNR,
PRODH,
NETWR
FROM LIPS.qvd;

// Load VBFA data
VBFA:
LOAD
VBELV,
VBELN
FROM VBFA.qvd;

// Load VBAK data
VBAK:
LOAD
NETWR,
VBELN
FROM VBAK.qvd;

// Perform the first join using Shared1
JOIN(LI_KP_LIPS):
LOAD
LIKP.*,
LIPS.*
RESIDENT LIKP;

// Perform the second join using Shared1
JOIN(LI_KP_LIPS_VBFA):
LOAD
LI_KP_LIPS.*,
VBFA.*
RESIDENT LI_KP_LIPS;

// Perform the third join using Shared2
JOIN(LI_KP_LIPS_VBFA_VBAK):
LOAD
LI_KP_LIPS_VBFA.*,
VBAK.*
RESIDENT LI_KP_LIPS_VBFA;

// Drop intermediate tables
DROP TABLES LIKP, LIPS, VBFA, VBAK;

// Rename the final table
LI_KP_LIPS_VBFA_VBAK:
RENAME TABLE LI_KP_LIPS_VBFA_VBAK TO FinalTable;

// Output the final result
FinalTable:
LOAD *;

Re: Multiple table join but with field switch in the middle.

panosalexand — Thu, 23 Nov 2023 08:06:05 GMT

Hello,

I want to share my point of view, but always, the key is what you need to show for your organization.
However, I'd like to show you the path I've chosen for these tables for one of my projects.

Keep in mind the same tables for other projects keep different paths.

//----------------------------------------------------------VBAK--------------------------------------------------//

QUALIFY *;

UNQUALIFY Sales_Order, OBJNR, WERKS,Consignee, YSD_YYYYMM, VDATU, AUART;

VBAK:

LOAD *,
VBELN AS Sales_Order

Resident Delta_VBAK;

DROP Table Delta_VBAK;

UNQUALIFY *;

//----------------------------------------------------------------------------------------------------------------------------//

//----------------------------------------------------------VBAP--------------------------------------------------//

QUALIFY *;

UNQUALIFY Sales_Order, MATNR, VBAPLIPS, VBAPVBFA;

VBAP:

LOAD VBELN AS Sales_Order,

VBELN&POSNR AS VBAPLIPS,

VBELN&POSNR AS VBAPVBFA

Resident Delta_VBAP;

DROP Table Delta_VBAP;

UNQUALIFY *;

//----------------------------------------------------------------------------------------------------------------------------//

//----------------------------------------------------------LIKP--------------------------------------------------//
QUALIFY *;
UNQUALIFY Key ;
LIKP:
LOAD VBELN as Key,
VBELN

from nodelta\LIKP.qvd(qvd);
UNQUALIFY *;
//----------------------------------------------------------------------------------------------------------------------------//

//----------------------------------------------------------LIPS--------------------------------------------------//
QUALIFY *;
UNQUALIFY Key, VBAPLIPS;
LIPS:
LOAD

VBELN as Key,
VGBEL&VGPOS as VBAPLIPS,
MATNR,
LGMNG,
VGBEL

Resident Delta_LIPS;
DROP Table Delta_LIPS;
UNQUALIFY *;
//----------------------------------------------------------------------------------------------------------------------------//

//----------------------------------------------------------MARA--------------------------------------------------//
QUALIFY *;
UNQUALIFY MATNR;
MARA:
Load MATNR,
MATKL,
MEINS,
PRDHA,
MTART

from \nodelta\MARA.qvd(qvd);
UNQUALIFY *;
//------------------------------------------------------------------------------------------------------------------//

//----------------------------------------------------------MAKT---------------------------------------------------//
QUALIFY *;
UNQUALIFY MATNR, SPRAS;
MAKT:
Load MATNR,
SPRAS,
MAKTX
from \nodelta\MAKT.qvd(qvd);
UNQUALIFY *;
//------------------------------------------------------------------------------------------------------------------//

//----------------------------------------------------------VBFA--------------------------------------------------//
QUALIFY *;
UNQUALIFY VBAPVBFA, LIPSTempVBFA ;
VBFA:
LOAD *,

VBELV&POSNV as VBAPVBFA,
VBELN&POSNN as LIPSTempVBFA

Resident Delta_VBFA
where VBTYP_N='C' ; // and VBTYP_V='C'
DROP Table Delta_VBFA;
UNQUALIFY *;

//---------------------------------------------------------------------------------------------------//

//---------------------------------------------------------LIKP_Temp--------------------------------------------------//

QUALIFY *;
UNQUALIFY Key2;
LIKP_Temp:
LOAD *,
LIKP.VBELN as Key2
Resident LIKP;

UNQUALIFY *;
//---------------------------------------------------------LIPS_Temp--------------------------------------------------//

QUALIFY *;
UNQUALIFY LIPSTempVBFA, Key2
LIPS_Temp:
LOAD *,
LIPS.VBELV&LIPS.POSNV as LIPSTempVBFA,
LIPS.VBELN as Key2

Resident LIPS;

UNQUALIFY *;

Re: Multiple table join but with field switch in the middle.

Daryn — Thu, 23 Nov 2023 08:15:26 GMT

Thank you for your time and expertise, I will look at this later. Regards, Daryn

Re: Multiple table join but with field switch in the middle.

Daryn — Thu, 23 Nov 2023 08:15:35 GMT

Thank you for your time and expertise, I will look at this later. Regards, Daryn

Re: Multiple table join but with field switch in the middle.

Daryn — Thu, 23 Nov 2023 15:16:34 GMT

Hi Awm,

I seem to encounter errors with this;

Thanks again, Daryn