Qlik Community

QlikView Performance

Discussion Board for collaboration on QlikView Performance.

olivetwist
New Contributor III

Where Not Exists()

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.

1 Solution

Accepted Solutions
atkinsow
Valued Contributor II

Re: Where Not Exists()

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;

11 Replies
atkinsow
Valued Contributor II

Re: Where Not Exists()

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;

olivetwist
New Contributor III

Re: Where Not Exists()

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. "

atkinsow
Valued Contributor II

Re: Where Not Exists()

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.

olivetwist
New Contributor III

Re: Where Not Exists()

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.

atkinsow
Valued Contributor II

Re: Where Not Exists()

I can't necessarily disagree.   

Glad you got it working though!

atkinsow
Valued Contributor II

Re: Where Not Exists()

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

olivetwist
New Contributor III

Re: Where Not Exists()

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.

atkinsow
Valued Contributor II

Re: Where Not Exists()

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

olivetwist
New Contributor III

Re: Where Not Exists()

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.

Community Browser