topic Re: Where Not Exists() in QlikView

Where Not Exists()

olivetwist — Mon, 26 Jan 2026 18:19:17 GMT

I am trying to improve load times and have been updating code to allow for efficiency. one of the changes i was making is in regards to the Exists() function. my current script looks like this:

LOAD
    test_ID,
    test_ACTIVITY_TS,
    test_ACTIVITY_TYP_CD
FROM $(WorkflowPath)\test.QVD (qvd)
where test_SYSTEM_CD <> 'CWF';

and this works, but when I change "where test_SYSTEM_CD <> 'CWF';" to "where not exists(test_SYSTEM_CD,'CWF');" it returns 0 results. this would probably only save me a few seconds, but i am more interested in why this wouldn't work.

Thanks in advance.

Re: Where Not Exists()

Anonymous — Fri, 14 Jul 2017 17:38:55 GMT

You have to refer to an already loaded field. And the field has to be in your load script too. You can refer to a value like this to do it this way I would do this:

Filter:

LOAD * INLINE [

test_SYSTEM_CD

CWF

];

Load

test_ID,

test_ACTIVITY_TS,

test_ACTIVITY_TYP_CD,

test_SYSTEM_CD

From YourQVD (qvd)

where not exists(test_SYSTEM_CD);

Drop table Filter;

Re: Where Not Exists()

olivetwist — Fri, 14 Jul 2017 17:43:46 GMT

If that is true, then why does Qlikview help say this:

"exists(Month, 'Jan') returns -1 (true) if the field value 'Jan' is found in the current content of the field Month. "

Re: Where Not Exists()

Anonymous — Fri, 14 Jul 2017 17:47:54 GMT

Also says in the first line:

"These functions are used when a value from previously loaded records of data is needed for the evaluation of the current record. "

The way you have it, it is not previously loaded.

Re: Where Not Exists()

olivetwist — Fri, 14 Jul 2017 17:49:27 GMT

I understand that, but 'Jan' is also not a previously loaded value, it is a string. i guess it is just a VERY BAD and WRONG example.

Re: Where Not Exists()

Anonymous — Fri, 14 Jul 2017 17:51:05 GMT

I can't necessarily disagree.

Glad you got it working though!

Re: Where Not Exists()

Anonymous — Fri, 14 Jul 2017 17:53:04 GMT

By that example, you could try below, but I don't know if it would work or if it would still be an optimized load. If you do try it and it works let me know.

Load

test_ID,

test_ACTIVITY_TS,

test_ACTIVITY_TYP_CD,

test_SYSTEM_CD

From YourQVD (qvd)

where not exists(test_SYSTEM_CD,'CWF');

Re: Where Not Exists()

olivetwist — Fri, 14 Jul 2017 17:55:50 GMT

That is exactly what i was trying in my orig example.

edit:

ok, not EXACTLY, i didnt include the test_SYSTEM_CD field in the load of my example.

Re: Where Not Exists()

Anonymous — Fri, 14 Jul 2017 17:58:15 GMT

Your original example didn't have test_SYSTEM_CD in your load script.

You didn't have the bolded part below.

Load

test_ID,

test_ACTIVITY_TS,

test_ACTIVITY_TYP_CD,

test_SYSTEM_CD

From YourQVD (qvd)

where not exists(test_SYSTEM_CD,'CWF');

Re: Where Not Exists()

olivetwist — Fri, 14 Jul 2017 19:30:00 GMT

Just as an FYI, it does work with a string like 'CWF' as long as the field is included in the load statement. With other functions and fields used in the where statement you don't need to include it so I wasn't expecting it to be any different.

Re: Where Not Exists()

Anonymous — Fri, 14 Jul 2017 19:32:24 GMT

Good to know. Thanks.

Was the load still optimized that way?

Re: Where Not Exists()

olivetwist — Fri, 14 Jul 2017 19:33:06 GMT

Yes it was. shaved 1.5 min. every little bit helps.