Where exists function giving error in Qlik Sense Nov 2018 release

[stefanvb]

Hi all,

I am sure I am not the only one. 

I just upgraded my platform with the latest release (November - 2018 ) and I encountered a few errors in my loads.
It seems like the 'WHERE EXISTS' function works differently within the November release.

Am I correct?

[Nikos_Lamprinakos]

Hi Marcus,

I think it mostly impacts the scenario you mention, as these are were one can benefit from optimized loads.

Slightly changing your example, you will get an error in the following scenarios:

Scenario 1: Not loading the YEAR field in the table

dummy: load year(today()) as YEAR autogenerate 1;

table: load 

[field 1]
,[field 2]

 from QVD where exists(YEAR);

drop tables dummy;

-this will result in 'unknown load statement error'

Scenario 2: Rename YEAR in table

dummy: load year(today()) as YEAR autogenerate 1;

table: load 

[field 1]
,[field 2]
,YEAR as [Sales Year]

 from QVD where exists(YEAR);

drop tables dummy;

-this will result in 'unknown load statement error'

You can avoid the error with Exists(YEAR,YEAR), but the load will not be optimized.

My workaround so far to keep the load optimized is to include the field without renaming it, and then dropping it. It however feels counter-intuitive so I would really like your suggestions.

Nikos

[marcus_sommer]

I could admit that both scenarios work with older releases (at least QV 11.2 SR12). Currently I couldn't remember that I use exists() anywhere in such a way (because the exists-field is an essential part of the - usually - incremental loadings and also there is seldom a need to rename it) and I assume it as rather unlikely that I would do it in the future.

Nevertheless it would be interesting why Qlik has changed this functionality and what others think about it:

Henric Cronström

Rob Wunderlich

Stefan Wühl

Gysbert Wassenaar

[nsm1234567]

Yeah, a response on this would be really helpful as this is going to have a huge impact in our environment.  Almost all of our loads in Qlikview and Qliksense use this method to keep the load optimised.  We've also discovered that if a particular field is not included in the load of the where exists it'll fail.  So the below sort of code:  which used to work for us now fails:

TABLE1:

LOAD companyNumber

FROM QVD;

TABLE2:

LOAD someOtherField

FROM SOME_OTHER_QVD_THAT_CONTAINS_COMPANYNUMBEr

WHERE EXISTS companyNumber;

This is extremely frustrating!

[sergio_loza]

Has there been any update on how to maintain a load optimized while using Exists on a single field? Every single article about tricks to keep loads optimized and reducing data point to the use of exists.

[nsm1234567]

Hey there,

I ended up sending this on to Qlik and a lot of the Where Exists related issues were fixed in the February 2019 release.  Is it an option for you to move to that release?  Not sure if you're using Sense server.  We found that upgrading to Feb 2019 resolved a lot of the issues (we just skipped November altogether).

I believe on Server side there is also a new patch for November 2018, but not sure if that is included in the fix.

[sergio_loza]

thanks for the info, we are using Feb2018 and evaluating moving to November 2018.
What are some of the issues that you saw gotten fixed in Feb 2019 if i may ask

[nsm1234567]

The specific issues we experienced related to a bug spoken about here:  https://qliksupport.force.com/articles/000060637

If you look at one of my previous comments above, you'll see I outlined some specific where exists scenarios that were failing after the behaviour change in Nov 2018.  Because we handle a lot of our loads using that sort of logic we ended up skipping Nov 2018 and going straight to Feb 2019.

I'm not sure what your company's pattern/policy is for upgrades but we've found Feb 2019 to be a pretty good and stable release and we haven't had any issues thus far (we have a decent-sized Qliksense deployment with 100 users and 30 apps).

[stefanvb]

Thanks (and all others) for the input.

I did an upgrade for another client to the Feb-19 (and now APR-19) release and I'm quite happy with this release.
I can recommend deploying this release if the situation allows.

It might be good Qlik tackle this issues on forehand preventing any surprises for developers.

Regards,

Stefan

[EveLena]

hello,

we had the same issue in Qlikview when we made the update from 12.0.20200 SR3  to QV April 19 SR3 V 12.4.20300.0

The where exist function was in some special cases not working. (I think it's because we made a Key from three fields while when it was working it was a database field.)

I did it almost the same way as  @Nikos_Lamprinakos 

I did now first load the table and generate the key befor joining the table to the other table and having the exist statement by the join part.
Afterwards I deleted the table I loaded in the first step, and now it works fine.

[nsm1234567]

Hey there, 

I believe this was a bug as it was fixed in subsequent releases of Sense.  On Qlikview, we're running April 2019 SR1 and haven't run into the issues you've mentioned here, but thanks for the heads up.