Qlik Community

Ask a Question

QlikView App Dev

Discussion Board for collaboration related to QlikView App Development.

Announcements
Our next Qlik Insider session will cover new key capabilities. Join us August 11th REGISTER TODAY
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

View solution in original post

danielle_v
Creator
Creator
Author

Thanks All, I appreciate your help!

All really helpful answers 🙂