Qlik Community

Qlik Sense App Development

Discussion board where members can learn more about Qlik Sense App Development and Usage.

Nikos_Lamprinakos
New Contributor

Re: Where exists function giving error in Qlik Sense Nov 2018 release

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: Where exists function giving error in Qlik Sense Nov 2018 release

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
Contributor

Re: Where exists function giving error in Qlik Sense Nov 2018 release

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
New Contributor III

Re: Where exists function giving error in Qlik Sense Nov 2018 release

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
Contributor

Re: Where exists function giving error in Qlik Sense Nov 2018 release

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
New Contributor III

Re: Where exists function giving error in Qlik Sense Nov 2018 release

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
Contributor

Re: Where exists function giving error in Qlik Sense Nov 2018 release

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