Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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
