topic Re: Where Exists in QlikView

Where Exists

danielle_v — Wed, 13 May 2015 13:52:59 GMT

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!

Re: Where Exists

rajeshvaswani77 — Wed, 13 May 2015 13:59:55 GMT

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

Re: Where Exists

Wed, 13 May 2015 14:00:07 GMT

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

Re: Where Exists

rubenmarin — Wed, 13 May 2015 14:02:54 GMT

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.

Re: Where Exists

datanibbler — Wed, 13 May 2015 14:03:34 GMT

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

Re: Where Exists

danielle_v — Wed, 13 May 2015 15:10:44 GMT

Thanks All, I appreciate your help!

All really helpful answers 🙂