topic Re: WHERE NOT EXISTS work not correctly in QlikView

WHERE NOT EXISTS work not correctly

warfollowmy_ver — Fri, 02 Dec 2016 07:56:32 GMT

1) There are several QVD files from one structure.

2) It is necessary to download all the files through the '*.qvd (qvd)' because the file becomes larger with different names.

3) It is necessary to download everything but without 'C'.

Examples of the attachment

Example 'LOAD ALL' load all. OK.

Example 'LOAD EXISTS' load only listed with 'A' and 'B'. OK.

Example 'LOAD NOT EXISTS' load everything but without 'C'. ??? Who knows what's going on? 😃

Re: WHERE NOT EXISTS work not correctly

tresB — Fri, 02 Dec 2016 08:16:58 GMT

Not exists seems to be working fine. What is your expected out put?

Re: WHERE NOT EXISTS work not correctly

warfollowmy_ver — Fri, 02 Dec 2016 09:44:49 GMT

You are joking? 😃

Obviously must be load all values without 'C' but was be loaded not all values off A and B.

Load only first rows A and B.

Please look QVD files or second and first example, look number of rows and values of A and B...

Re: WHERE NOT EXISTS work not correctly

swuehl — Fri, 02 Dec 2016 09:58:12 GMT

Are you looking for

NoConcatenate

LOAD * INLINE [

'GroupFilter'

'C'

];

t0:

LOAD * FROM *.qvd (qvd) WHERE NOT EXISTS (GroupFilter,Group);

Re: WHERE NOT EXISTS work not correctly

swuehl — Fri, 02 Dec 2016 09:59:34 GMT

As soon as the first value e.g. of Group 'A' is loaded from T1.qvd, it does exist in the symbol table...

Re: WHERE NOT EXISTS work not correctly

tresB — Fri, 02 Dec 2016 10:04:18 GMT

I am certainly not joking. Exists works like that. It just checks if the value is loaded so far. That is, is value 'A' is read in the first record, it would not load from subsequent rows, because it is already loaded (or, exists). It is kind of distinct load for a field value.

Hope this helps.

Re: WHERE NOT EXISTS work not correctly

warfollowmy_ver — Fri, 02 Dec 2016 10:39:50 GMT

Sorry. Now I understand.

Re: WHERE NOT EXISTS work not correctly

Anonymous — Fri, 02 Dec 2016 20:36:16 GMT

try where not match() vs where not exist()

Re: WHERE NOT EXISTS work not correctly

johnw — Fri, 02 Dec 2016 22:44:01 GMT

To get your intended behavior, what I typically do is load an extra copy of whatever the field is. So in this case, I would load an [Exclude Group] field. Then I'd load where not exists([Exclude Group],[Group]). Then I'd drop the extra field I'd created.

Edit: Which is to say I'd do it just like Stephan said. Never mind!

Re: WHERE NOT EXISTS work not correctly

johnw — Fri, 02 Dec 2016 22:47:09 GMT

That works, at least if the list is small (as it is here), but it also breaks the optimized QVD load, so it will go much more slowly if these are large QVDs.