topic Selective loads from 2 QVDs in QlikView

Selective loads from 2 QVDs

william_denholm — Mon, 10 Aug 2015 08:21:50 GMT

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.

Re: Selective loads from 2 QVDs

MayilVahanan — Mon, 10 Aug 2015 08:31:40 GMT

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;

Re: Selective loads from 2 QVDs

Gysbert_Wassenaar — Mon, 10 Aug 2015 08:35:31 GMT

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.