4 Tables in one, Join Problems

DS395 · ‎2023-08-22

Hello forum members,

I have four tables that I want to merge into one:
The tables look something like this :

[Purchaseheader]:
LOAD * INLINE [
PURCHASE, PURINFO
P10003	, 'DN1233,DN1224'
P10004	, 'DN1235,INV1233'
P10005	, 'DN1236,INV1231'
P10006	, ''
];

[Purchasepos]:
LOAD * INLINE [
PURHEAD	, PURPOS, PURITEM	, ORDQTY
P10003	, 1		, A1234		, 123
P10003	, 2		, A1235		, 20
P10003	, 3		, A1236		, 12
P10003	, 4		, A1237		, 3
P10003	, 5		, A1237		, 10
P10004	, 1		, A1237		, 20
P10004	, 2		, A1238		, 30
P10004	, 3		, A1236		, 2
P10005	, 1		, A1239		, 20
P10005	, 2		, A1210		, 30
P10005	, 3		, A1230		, 2
P10005	, 4		, A1235		, 30
P10005	, ''	, A1		, 0
P10006	, 1		, A1239		, 20
P10006	, 2		, A1210		, 30
];

[Deliveryheader]:
LOAD * INLINE [
DELIVERY, DELINFO
DN1233	, 'P10003'
DN1224	, 'P10003'
DN1235	, 'P10004,INV1233'
DN1236	, 'P10005,INV1231'
];

[Deliverypos]:
LOAD * INLINE [
DELHEAD	, DELPOS, DELITEM	, DELQTY
DN1233	, 1		, A1234		, 123
DN1233	, 2		, A1235		, 19
DN1224	, 4		, A1237		, 3
DN1224	, 5		, A1237		, 10
DN1235	, 1		, A1237		, 20
DN1235	, 2		, A1238		, 10
DN1235	, 3		, A1236		, 2
DN1236	, 1		, A1239		, 15
DN1236	, 2		, A1210		, 20
DN1236	, 3		, A1230		, 2
DN1236	, 4		, A1235		, 30
];

And the result should look like this:

//##############################RESULT###################################//
[RESULT]:
LOAD * INLINE [
PURCHASE, PURPOS, PURITEM	, ORDQTY, DELIVERY	, DELQTY
P10003	, 1		, A1234		, 123	, DN1233	, 123
P10003	, 2		, A1235		, 20	, DN1233	, 19
P10003	, 3		, A1236		, 12	, ''		,''
P10003	, 4		, A1237		, 3		, DN1224	, 3
P10003	, 5		, A1237		, 10	, DN1224	, 10
P10004	, 1		, A1237		, 20	, DN1235	, 20
P10004	, 2		, A1238		, 30	, DN1235	, 10
P10004	, 3		, A1236		, 2		, DN1235	, 2
P10005	, 1		, A1239		, 20	, DN1236	, 15
P10005	, 2		, A1210		, 30	, DN1236	, 20
P10005	, 3		, A1230		, 2		, DN1236	, 2
P10005	, 4		, A1235		, 30	, DN1236	, 30
P10005	, ''	, A1		, 0		,''			,''
P10006	, 1		, A1239		, 20	,''			,''
P10006	, 2		, A1210		, 30	,''			,''
];

However, I only get the orders as a result where a delivery note is also stored. See here my Example Code:

[Purchaseheader]:
LOAD * INLINE [
PURCHASE, PURINFO
P10003	, 'DN1233,DN1224'
P10004	, 'DN1235,INV1233'
P10005	, 'DN1236,INV1231'
P10006	, ''
];

[Purchase]:
LOAD
* 
WHERE LEFT([SUBINFO P],2) = 'DN';
LOAD 
	 [PURCHASE]						AS [%KEY_Purchaseheader_Purchasepos]
     
    ,[PURCHASE]						AS [PURCHASE] 
    ,TRIM(SUBFIELD([PURINFO],','))	AS [SUBINFO P]
RESIDENT 
	 [Purchaseheader];
DROP TABLE [Purchaseheader];

[Purchasepos]:
LOAD * INLINE [
PURHEAD	, PURPOS, PURITEM	, ORDQTY
P10003	, 1		, A1234		, 123
P10003	, 2		, A1235		, 20
P10003	, 3		, A1236		, 12
P10003	, 4		, A1237		, 3
P10003	, 5		, A1237		, 10
P10004	, 1		, A1237		, 20
P10004	, 2		, A1238		, 30
P10004	, 3		, A1236		, 2
P10005	, 1		, A1239		, 20
P10005	, 2		, A1210		, 30
P10005	, 3		, A1230		, 2
P10005	, 4		, A1235		, 30
P10005	, ''	, A1		, 0
P10006	, 1		, A1239		, 20
P10006	, 2		, A1210		, 30
];

LEFT JOIN (Purchase)
LOAD 
	 [PURHEAD]						AS [%KEY_Purchaseheader_Purchasepos]
     
    ,[PURPOS]						AS [PURPOS]
	,[PURITEM]						AS [PURITEM]
    ,[ORDQTY]						AS [ORDQTY]
RESIDENT 
	 [Purchasepos];
DROP TABLE [Purchasepos];

[Deliveryheader]:
LOAD * INLINE [
DELIVERY, DELINFO
DN1233	, 'P10003'
DN1224	, 'P10003'
DN1235	, 'P10004,INV1233'
DN1236	, 'P10005,INV1231'
];

[Deliverynotes]:
LOAD
* 
WHERE LEFT([SUBINFO DN],1) = 'P';
LOAD 
	 [DELIVERY]						AS [%KEY_Deliveryheader_Deliverypos]
     
    ,[DELIVERY]						AS [DELIVERY] 
    ,TRIM(SUBFIELD([DELINFO],','))	AS [SUBINFO DN]
RESIDENT 
	 [Deliveryheader];
DROP TABLE [Deliveryheader];

[Deliverypos]:
LOAD * INLINE [
DELHEAD	, DELPOS, DELITEM	, DELQTY
DN1233	, 1		, A1234		, 123
DN1233	, 2		, A1235		, 19
DN1224	, 4		, A1237		, 3
DN1224	, 5		, A1237		, 10
DN1235	, 1		, A1237		, 20
DN1235	, 2		, A1238		, 10
DN1235	, 3		, A1236		, 2
DN1236	, 1		, A1239		, 15
DN1236	, 2		, A1210		, 20
DN1236	, 3		, A1230		, 2
DN1236	, 4		, A1235		, 30
];

LEFT JOIN (Deliverynotes)
LOAD 
	 [DELHEAD]						AS [%KEY_Deliveryheader_Deliverypos]
     
    ,[DELPOS]						AS [DELPOS]
	,[DELITEM]						AS [DELITEM]
    ,[DELQTY]						AS [DELQTY]
RESIDENT 
	 [Deliverypos];
DROP TABLE [Deliverypos];

[RESIDENT_PURCHASE]:
LOAD
	 [SUBINFO P]&'|'&[PURPOS]&'|'&[PURITEM]	AS [%KEY_IJ_Purchase_Deliverynotes]
     
	,[PURCHASE]
	,[SUBINFO P]
	,[PURPOS]
	,[PURITEM]
	,[ORDQTY]
RESIDENT 
	 [Purchase];
DROP TABLE [Purchase];

INNER JOIN ([RESIDENT_PURCHASE])
[RESIDENT_DELIVERYNOTES]:
LOAD
	 [DELIVERY]&'|'&[DELPOS]&'|'&[DELITEM]	AS [%KEY_IJ_Purchase_Deliverynotes]
     
	,[DELIVERY]
	,[SUBINFO DN]
	,[DELPOS]
	,[DELITEM]
	,[DELQTY]
RESIDENT 
	 [Deliverynotes];
DROP TABLE [Deliverynotes];

How do I still get the positions where there is no delivery note? Thats my Result:

Can anyone give me some food for thought? Or is my approach too complicated and there is a simpler way?

Many thanks in advance.

Greetings

DS395

DS395 · ‎2023-08-22

Hello again,

I have found a solution myself, but in my eyes it is very awkward. At the end, I loaded the order table again with the individual positions and then connected this with the previously existing list with a LEFT JOIN:

[Purchaseheader]:
LOAD * INLINE [
PURCHASE, PURINFO
P10003	, 'DN1233,DN1224'
P10004	, 'DN1235,INV1233'
P10005	, 'DN1236,INV1231'
P10006	, ''
];

[Purchase]:
LOAD
* 
WHERE LEFT([SUBINFO P],2) = 'DN';
LOAD 
	 [PURCHASE]						AS [%KEY_Purchaseheader_Purchasepos]
     
    ,[PURCHASE]						AS [PURCHASE] 
    ,TRIM(SUBFIELD([PURINFO],','))	AS [SUBINFO P]
RESIDENT 
	 [Purchaseheader];
DROP TABLE [Purchaseheader];

[Purchasepos]:
LOAD * INLINE [
PURHEAD	, PURPOS, PURITEM	, ORDQTY
P10003	, 1		, A1234		, 123
P10003	, 2		, A1235		, 20
P10003	, 3		, A1236		, 12
P10003	, 4		, A1237		, 3
P10003	, 5		, A1237		, 10
P10004	, 1		, A1237		, 20
P10004	, 2		, A1238		, 30
P10004	, 3		, A1236		, 2
P10005	, 1		, A1239		, 20
P10005	, 2		, A1210		, 30
P10005	, 3		, A1230		, 2
P10005	, 4		, A1235		, 30
P10005	, ''	, A1		, 0
P10006	, 1		, A1239		, 20
P10006	, 2		, A1210		, 30
];

LEFT JOIN (Purchase)
LOAD 
	 [PURHEAD]						AS [%KEY_Purchaseheader_Purchasepos]
     
    ,[PURPOS]						AS [PURPOS]
	,[PURITEM]						AS [PURITEM]
    ,[ORDQTY]						AS [ORDQTY]
RESIDENT 
	 [Purchasepos];
DROP TABLE [Purchasepos];

[Deliveryheader]:
LOAD * INLINE [
DELIVERY, DELINFO
DN1233	, 'P10003'
DN1224	, 'P10003'
DN1235	, 'P10004,INV1233'
DN1236	, 'P10005,INV1231'
];

[Deliverynotes]:
LOAD
* 
WHERE LEFT([SUBINFO DN],1) = 'P';
LOAD 
	 [DELIVERY]						AS [%KEY_Deliveryheader_Deliverypos]
     
    ,[DELIVERY]						AS [DELIVERY] 
    ,TRIM(SUBFIELD([DELINFO],','))	AS [SUBINFO DN]
RESIDENT 
	 [Deliveryheader];
DROP TABLE [Deliveryheader];

[Deliverypos]:
LOAD * INLINE [
DELHEAD	, DELPOS, DELITEM	, DELQTY
DN1233	, 1		, A1234		, 123
DN1233	, 2		, A1235		, 19
DN1224	, 4		, A1237		, 3
DN1224	, 5		, A1237		, 10
DN1235	, 1		, A1237		, 20
DN1235	, 2		, A1238		, 10
DN1235	, 3		, A1236		, 2
DN1236	, 1		, A1239		, 15
DN1236	, 2		, A1210		, 20
DN1236	, 3		, A1230		, 2
DN1236	, 4		, A1235		, 30
];

LEFT JOIN (Deliverynotes)
LOAD 
	 [DELHEAD]						AS [%KEY_Deliveryheader_Deliverypos]
     
    ,[DELPOS]						AS [DELPOS]
	,[DELITEM]						AS [DELITEM]
    ,[DELQTY]						AS [DELQTY]
RESIDENT 
	 [Deliverypos];
DROP TABLE [Deliverypos];

[RESIDENT_PURCHASE]:
LOAD
	 [SUBINFO P]&'|'&[PURPOS]&'|'&[PURITEM]	AS [%KEY_IJ_Purchase_Deliverynotes]
     
	,[PURCHASE]
	,[SUBINFO P]
	,[PURPOS]
	,[PURITEM]
	,[ORDQTY]
RESIDENT 
	 [Purchase];
     
DROP TABLE [Purchase];

INNER JOIN ([RESIDENT_PURCHASE])
[RESIDENT_DELIVERYNOTES]:
LOAD
	 [DELIVERY]&'|'&[DELPOS]&'|'&[DELITEM]	AS [%KEY_IJ_Purchase_Deliverynotes]
     
	,[DELIVERY]
	,[SUBINFO DN]
	,[DELPOS]
	,[DELITEM]
	,[DELQTY]
RESIDENT 
	 [Deliverynotes];
DROP TABLE [Deliverynotes];


//######################################################################################################

[TEMP_Purchaseheader]:
LOAD * INLINE [
PURCHASE, PURINFO
P10003	, 'DN1233,DN1224'
P10004	, 'DN1235,INV1233'
P10005	, 'DN1236,INV1231'
P10006	, ''
];

[TEMP_Purchase]:
LOAD 
	 [PURCHASE]						AS [%KEY_TEMP_Purchaseheader_Purchasepos]
     
    ,[PURCHASE]						AS [TEMP_PURCHASE] 
RESIDENT 
	 [TEMP_Purchaseheader];
DROP TABLE [TEMP_Purchaseheader];

[TEMP_Purchasepos]:
LOAD * INLINE [
PURHEAD	, PURPOS, PURITEM	, ORDQTY
P10003	, 1		, A1234		, 123
P10003	, 2		, A1235		, 20
P10003	, 3		, A1236		, 12
P10003	, 4		, A1237		, 3
P10003	, 5		, A1237		, 10
P10004	, 1		, A1237		, 20
P10004	, 2		, A1238		, 30
P10004	, 3		, A1236		, 2
P10005	, 1		, A1239		, 20
P10005	, 2		, A1210		, 30
P10005	, 3		, A1230		, 2
P10005	, 4		, A1235		, 30
P10005	, ''	, A1		, 0
P10006	, 1		, A1239		, 20
P10006	, 2		, A1210		, 30
];

LEFT JOIN (TEMP_Purchase)
LOAD 
	 [PURHEAD]								AS [%KEY_TEMP_Purchaseheader_Purchasepos]
     
    ,[PURPOS]								AS [TEMP_PURPOS]
	,[PURITEM]								AS [TEMP_PURITEM]
    ,[ORDQTY]								AS [TEMP_ORDQTY]
RESIDENT 
	 [TEMP_Purchasepos];
DROP TABLE [TEMP_Purchasepos];

[Purchase_RESULT]:
LOAD 
	 [TEMP_PURCHASE]&'|'&[TEMP_PURPOS]&'|'&[TEMP_PURITEM]		AS [%KEY_LJ_PurchaseRESULT_RESIDENTPURCHASE]
     
	,[TEMP_PURCHASE]
	,[TEMP_PURPOS]
	,[TEMP_PURITEM]
	,[TEMP_ORDQTY]
RESIDENT 
	 [TEMP_Purchase];
DROP TABLE [TEMP_Purchase];

LEFT JOIN ([Purchase_RESULT])
LOAD
	 [PURCHASE]&'|'&[PURPOS]&'|'&[PURITEM]						AS [%KEY_LJ_PurchaseRESULT_RESIDENTPURCHASE]
     
	,[PURCHASE]
	,[SUBINFO P]
	,[PURPOS]
	,[PURITEM]
	,[ORDQTY]
	,[DELIVERY]
	,[SUBINFO DN]
	,[DELPOS]
	,[DELITEM]
	,[DELQTY]
RESIDENT
	 [RESIDENT_PURCHASE];
DROP TABLE [RESIDENT_PURCHASE];

If anyone has another solution or even better knows a simpler solution, I would be grateful for any information.

Result:

Greetings

DS395

Data Load Editor

Developers

General Question

Script