Selective loads from 2 QVDs

[william_denholm]

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.

[MayilVahanan]

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;

[Gysbert_Wassenaar]

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.