Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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.
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;
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;
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. "
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.
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.
I can't necessarily disagree.
Glad you got it working though!
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');
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.
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');
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.