Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
danielle_v
Creator
Creator

Where Exists

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!

1 Solution

Accepted Solutions
datanibbler
Champion
Champion

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

View solution in original post

5 Replies
rajeshvaswani77
Specialist III
Specialist III

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

Not applicable

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);


rubenmarin

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.

datanibbler
Champion
Champion

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

danielle_v
Creator
Creator
Author

Thanks All, I appreciate your help!

All really helpful answers 🙂