Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello,
I have two tables that I want to load information from but only where certain conditions are met. I only want those message type of Order_Confirmation and I want to match that to a second table to get the Status within the message.
ORDERS table
Message ID | Order ID | Message type |
---|---|---|
1 | 1001001 | Order_Confirmation |
2 | 1001002 | Order_Confirmation |
3 | 1001110 | Order_Cancellation |
ORDER Lines table
Message ID | Order Line | Status |
---|---|---|
1 | 1 | Confirmed |
1 | 2 | Confirmed |
2 | 1 | Delayed |
I want to match the order ID in ORDERS table with the Status in the ORDER Lines table.
The message IDs match, and I only want to take Order Line 1 from the order line table.
I've tried to do a concatenate load of the ORDER Lines table where the message type is Order_Confirmation and then use those Message IDs to load from the Order Lines table but that doesn't work.
LOAD MESSAGE_ID,
ORDER_LINE,
STATUS
FROM
[ORDER_LINES.qvd]
(qvd)
WHERE MATCH(ORDER_LINE, '1');
Concatenate LOAD
MESSAGE_ID
FROM
[ORDERS.qvd](qvd)
WHERE NOT EXISTS(MESSAGE_ID)
AND MATCH(MESSAGE_TYPE, 'ORDER_CONFIRMATION');
Any help would be gratefully appreciated.
Hi
Try like this
Message ID | Message type | Order ID | Order Line | Status |
---|---|---|---|---|
1 | Order_Confirmation | 1001001 | 1 | Confirmed |
2 | Order_Confirmation | 1001002 | 1 | Delayed |
OrdersTemp:
LOAD * Where WildMatch([Message type],'*Confirmation*');
LOAD * INLINE [
Message ID, Order ID, Message type
1, 1001001, Order_Confirmation
2, 1001002, Order_Confirmation
3, 1001110, Order_Cancellation
];
Left Join(OrdersTemp)
LOAD * INLINE [
Message ID, Order Line, Status
1, 1, Confirmed
1, 2, Confirmed
2, 1, Delayed
];
NoConcatenate
Order:
LOAD * Resident OrdersTemp Where [Order Line] = 1;
DROP Table OrdersTemp;
I only want those message type of Order_Confirmation
Load * From [ORDERS table] Where [Message Type] = 'Order_Confirmation';
I want to match that to a second table to get the Status within the message
Load * From [ORDER Lines table];
The tables will be linked by the common field Message ID. So the Status will be associated with the correct orders.