Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
Announcing the newest addition to the Qlik Community, Qlik Gallery! Learn More
Highlighted
Not applicable

Where not exists what does this do?

Hello

I have a document wherein i have two loads

LOAD1

Trans:

LOAD

Key1

Key2

.......

from xxxx.qvd

Load2

Concatenate (trans)

LOAD

Key1

Key1 as KeyA

Key2

from xxxx.qvd where not exists(key1,KeyA)

What does it do? Key1 and KeyA is the same? Why would it find anything not matching?

Tags (2)
6 Replies
marcus_malinow
Valued Contributor III

Re: Where not exists what does this do?

It's not going to work as you have it.

If you are wanting to make sure that you only load a value into your second table where the related Key1 value has already been loaded in your first table then use Exists(Key1)

puttemans
Valued Contributor

Re: Where not exists what does this do?

Hi thomas,

Where not exists is a command you can use in order to exclude lines in your load. Although it seems you have a mistake in the script, as Marcus also mentions, the basic idea behind it is that you'll load everything from load 2, except the lines where Key1/A is similar to Key 1 from your Trans load.


You need to do the Trans load, in order for Qlikview to know the value you don't want to load in advance.

Regards,

Johan

Re: Where not exists what does this do?

The really good thing about Where & Where Not Exists is that if you use one, & only one of them, then a load from a qvd file remains optimized, as in really really fast.

So one can, for example, load a calendar of just this year's dates and then do a Where Exists ([Date]) load on a transaction qvd with decades of data an only load what want whilst still optimized.

Not applicable

Re: Where not exists what does this do?

Not exists checks previously loaded data so it is checking Key1 in table 1 against key A in table two. If you only want to return rows in table two which do not match a value of Key1 in table one without joining the tables this would work.

Note: You did not need to create a second field called KeyA, the code will do the same thing this way not exists(Key1)

Re: Where not exists what does this do?

This will not work for two reasons:

  1. KeyA cannot be used in the WHERE clause of the second LOAD because KeyA does not exist until after the LOAD statement has been executed til the end. You will get a FieldNotFound error.
  2. You are performing a WHERE NOT EXISTS from the same QVD as the first LOAD (xxxx.qvd). There is nothing more to add, when all rows have already been LOADed.

Peter

Not applicable

Re: Where not exists what does this do?

Hi Thomas,

have a read of the below, should help your understanding

http://community.qlik.com/docs/DOC-6866

hope that helps

Joe