Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
olivetwist
Creator
Creator

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
Anonymous
Not applicable

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;

View solution in original post

11 Replies
Anonymous
Not applicable

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
Creator
Creator
Author

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

Anonymous
Not applicable

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
Creator
Creator
Author

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.

Anonymous
Not applicable

I can't necessarily disagree.   

Glad you got it working though!

Anonymous
Not applicable

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
Creator
Creator
Author

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.

Anonymous
Not applicable

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
Creator
Creator
Author

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.