topic QV9 vs QV10: Exists() in Where statement has different behavior in QlikView

QV9 vs QV10: Exists() in Where statement has different behavior

Sat, 15 Jan 2011 21:39:53 GMT

I run with QV10 some of my applications developed in QV9 and almost all worked fine. But some of them failed. I found that when I use a LOAD statement with a WHERE Not Exists(...) QV10 produce a different result than QV9.

I create an example application to show the difference. The load script is:

Table:
LOAD * INLINE [
F1, F2
A, 1
A, 2
A, 3
];

Table2:
LOAD * INLINE [
KeyField, Data
B, 1
];

CONCATENATE (Table2)

LOAD F1 AS KeyField
, F2 AS Data
RESIDENT Table
WHERE NOT Exists(KeyField, F1);

DROP TABLE Table;

After running the app in QV9 and QV10 Table2 has the following content:

QV9 doesn't inserts in Table2 repeated values of KeyField, like if it ignores values already inserted by LOAD statement. On the other hand, QV10 only ignores values on KeyField loaded before the LOAD statement.

[View:http://community.qlik.com/cfs-file.ashx/__key/CommunityServer.Components.UserFiles/00.00.01.15.11.Posted/QV9-vs-QV10-_2D00_-Exists.qvw]

QV9 vs QV10: Exists() in Where statement has different behavior

Sun, 16 Jan 2011 04:57:19 GMT

Hi Damian,

Let us take the things apart from Qv9 and Qv10 . Let us consider the Concatenate and Not Exists function . According to your example the output using these two functions will be the result that is displayed in QV10. Logically the output which is displayed in QV9 is not correct . Just check your release version in QV9 and upgrade if necessary .

As you said QV9 doesn't inserts in Table2 repeated values of KeyField, like if it ignores values already inserted by LOAD statement. On the other hand, QV10 only ignores values on KeyField loaded before the LOAD statement.Let me have a look and update you on this .

Regards,

Chakravarthy.

QV9 vs QV10: Exists() in Where statement has different behavior

Sun, 16 Jan 2011 12:41:52 GMT

Hi Chakravarthy,

Sure! The bug in QV9 SR6 keep hidden some potencial bugs in my applications.

Regards,

dd. //

QV9 vs QV10: Exists() in Where statement has different behavior

Wed, 16 Feb 2011 13:45:27 GMT

Hi Damian, recently moved to qv10 desktopn client for testing and can verify this change in behaviour.

I've been playing around with load statements and found that if I simply add in an additional field "rowno()" to Table2 (i.e. the table I want to constrain) my "not exists" clause behaves as intended and I only get 1 row per constraining value.

Try this out

Cheers, Jason

QV9 vs QV10: Exists() in Where statement has different behavior

johnw — Wed, 23 Feb 2011 20:22:54 GMT

I think the old behavior has been that way since I started using QlikView over five years ago, though I'm not certain. I don't consider it a bug. Both interpretations of exists() are perfectly logical.

The new way, exists() means "existed prior to this load". That's logical. Most of the time, in practical use at our company, that's what we're trying to do.

The old way, exists() means "exists PERIOD". That is ALSO logical. As soon as you add a value to your new table, it exists, does it not? Therefore, exists() detects that value. If you meant that it existed prior to your load, you could accomplish that by creating a duplicate ID in the old table(s) that you don't load in the new table.

The old way also supported certain functionality that seems like it will be more difficult in version 10. For instance, taking the first row for a given ID from some input:

LOAD * INLINE [
Dispute ID, ID
US-42354,US-1161514
US-42355,US-1870004
US-42355,US-1985414 <-- this row will be skipped
US-42356,US-2025188]
WHERE not exists("Dispute ID");

This approach no longer works in version 10, and since version 9 wasn't a bug (just a different logical interpretation of exists()), I'm unhappy with it being changed from a backwards compatibility perspective. Now, to switch to version 10, I have to track down anywhere we might be taking advantage of the old interpretation of exists().

Also, I'm not yet seeing a way as clean and fast as the above to duplicate the functionality in version 10. This works, but requires two loads, and assumes we want the lowest rather than first ID:

data:
LOAD * INLINE [
Dispute ID, ID
US-42354,US-1161514
US-42355,US-1870004
US-42355,US-1985414
US-42356,US-2025188];

final_data:
NOCONCATENATE
LOAD
"Dispute ID"
,minstring("ID") as "ID"
RESIDENT data
GROUP BY "Dispute ID"
;
DROP TABLE data;

I can think of other ways to solve it, but none as clean as what was available in version 9, which I consider now broken in version 10.

Perhaps QlikTech should have introduced a new function, existedbeforethisload(), to behave the new way, instead of breaking the existing function. I'm sure there's a shorter name, but that's the idea. Or perhaps they should have added a parameter to control the desired behavior, defaulting to the approach it had always used before. Water under the bridge now, I suppose.

Our applications breaking every time we upgrade is one of the things I really hate about QlikView, particularly if there are ways to introduce desired functionality without breaking existing functionality, such as what I mentioned above for this particular case. I think QlikTech handles new versions pretty poorly.

QV9 vs QV10: Exists() in Where statement has different behavior

Wed, 23 Feb 2011 21:05:35 GMT

John,

Thanks for identifying and explaining this. Have you logged this issue with Support?

QV9 vs QV10: Exists() in Where statement has different behavior

johnw — Wed, 23 Feb 2011 21:19:47 GMT

I haven't...

... and won't. The new behavior is, for I think most users most of the time, BETTER. I'd be surprised if the change wasn't completely intentional, and quite possibly due to user complaints about the old behavior.

I'm just upset with the way the change was handled, with the lack of backwards compatibility, not with the change itself. And at this point, with so many people having used QV10 for so long, the backwards compatibility argument is reversed. At this point, you wouldn't want to change it back, because a lot of QV10 users are surely counting on the new behavior.

So after a little grumbling, I'll accept that the new way is the new way. I might suggest on the customer portal as an idea that a parameter be added to give the old behavior. That would simplify fixing old code where the new behavior has broken it. But I actually doubt that the new behavior will break any of my applications in practice, even if I'll need to be certain before we upgrade. The example I gave was just an example, not something I pulled from one of our apps.

Thanks for your quick response, though. 🙂

QV9 vs QV10: Exists() in Where statement has different behavior

Wed, 23 Feb 2011 22:11:47 GMT

"Now, to switch to version 10, I have to track down anywhere we might be taking advantage of the old interpretation of exists()."

That's exactly what I did before the upgrade. Nor I'm happy with the way the change was handled.

QV9 vs QV10: Exists() in Where statement has different behavior

Wed, 13 Jul 2011 19:13:24 GMT

QV10 SR3 has news about this topic: "37002 'where not exists' is not working as expected". Where "as expected" seems to be "as in QV9".

I've tested my sample app, and NOT Exists in QV10 SR3 has the same behavior than QV9.