Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi All,
In my script, I am loading in 2 tables from QVDs - Orders and Shipments. In both of these tables there is a field called Key_Order, so the 2 tables are linked.
I need to load in Order first and then only load the records from Shipment where the Key_Order already exists in Orders. So if for example there is a record in Shipments where the Key_Order is ABCDE, but there is no corresponding record in Order, this should not be loaded in.
I thought about doing this using a left join, but I want to keep the 2 tables separate as I'll have to make a lot of changes to everythgin else in my report if I join them together.
I believe I have to use 'where exists', but I 'm not sure how to use this and can't find any examples in the community that I can make any sense of.
Any help much appreciated!
Hi,
in principle, it is easy.
- You load your ORDERS table first.
- Then you load your SHIPMENTS table and you just attached a clause like
>> WHERE EXISTS(Key_Order) <<
to your LOAD statement (in front of the closing semicolon)
In principle, if the field has the same name in both tables, you can thus write this with only one parameter
<=> This can be tricky, so I would advise you rename one of those fields (the one in the table loaded first, the ORDER table) to sth like >> Key_Order_v1 << and then
write the clause like
>> WHERE EXISTS(Key_Order_v1, Key_Order) <<
(you can rename it back afterwards if you want with the RENAME FIELD function).
HTH
Hi Danielle,
Just use in the second table where exists(Key_Order) it will understand that it is being referred to the previously referred table.
thanks,
Rajesh Vaswani
Hi ,
Try this
Table 1:
Load
Data,
Key_Order,
Key_Order as ExistKey
From
Table1;
Table2:
Load
Data2
Key_Order,
From
Table2
where Exists(ExistKey,Key_Order);
Hi Danielle, in example:
Order:
LOAD ...
Key_Order
FROM ... ;
Shipment:
LOAD ....
FROM ...
Where Exists('Key_Order', Key_Order);
In the 'Exists' function the first 'Key_Order' is the field already loaded in QV, the 2nd 'Key_Order' is the field in the shipment qvd.
Hi,
in principle, it is easy.
- You load your ORDERS table first.
- Then you load your SHIPMENTS table and you just attached a clause like
>> WHERE EXISTS(Key_Order) <<
to your LOAD statement (in front of the closing semicolon)
In principle, if the field has the same name in both tables, you can thus write this with only one parameter
<=> This can be tricky, so I would advise you rename one of those fields (the one in the table loaded first, the ORDER table) to sth like >> Key_Order_v1 << and then
write the clause like
>> WHERE EXISTS(Key_Order_v1, Key_Order) <<
(you can rename it back afterwards if you want with the RENAME FIELD function).
HTH
Thanks All, I appreciate your help!
All really helpful answers 🙂