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

Where exists function giving error in Qlik Sense Nov 2018 release

stefanvb — Sat, 16 Nov 2024 21:46:07 GMT

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?

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

AshutoshBhumkar — Thu, 06 Dec 2018 11:24:18 GMT

Hi,
Could you share a sample scenario ?

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

stefanvb — Thu, 06 Dec 2018 11:44:17 GMT

Hi Ashutosh

To illustrate the situation, please see example below:

This script no longer working:
Vendor:
LOAD
%Key_Vendor as %Key_Vendor_some_table,
Country

FROM [lib://Transform $(vEnvironment) $(vClient) (instance)/Vendor.qvd]
(qvd)
where exists(%Key_Vendor) ;

Working with workaround:
Vendor:
LOAD
%Key_Vendor,
%Key_Vendor as %Key_Vendor_some_table,
Country

FROM [lib://Transform $(vEnvironment) $(vClient) (instance)/Vendor.qvd]
(qvd)
where exists(%Key_Vendor) ;

DROP FIELD %Key_Vendor FROM Vendor;

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

lironbaram — Thu, 06 Dec 2018 11:49:01 GMT

i think i encountered this also

what i did is adding a preceding load and ran the exist statement there this solved the issue

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

marcus_sommer — Thu, 06 Dec 2018 13:12:00 GMT

The workaround prevents the load to be optimized, right? Then it looked that each measure which enforced a processing of the data would work. IMO it would be a quite serious bug because a lot of scenarious use this method especially by incremental approaches.

- Marcus

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

isorinrusu — Thu, 27 Dec 2018 10:37:43 GMT

It happens to me too. I had 3.2 and the same script is not working in Nov 18.

What's really strange it that it sometimes works. For example, it works here:

[V_DETALLES_TEMP]:
LOAD

right(FileBaseName(),3)& [COD_COBRO] as COD_COBRO,
right(FileBaseName(),3)&
[COD_LINEA_NEGOCIO] as [COD_LINEA_NEGOCIO_DETALLE_COBROS],
right(FileBaseName(),3)&
[COD_CONCEPTO]&
[INDICADOR_TIPO_CONCEPTO] as _#ClaveCONCEPTOTIPO,
[IMPORTE_BASE],
[COD_NAVIDIAN]
FROM [lib://Red.2.QVDExtr/V_DETALLES_COBRO_*.qvd]
(qvd)
where Exists([COD_COBRO]);

But not a few lines down:
[MAESTRA_CONCEPTO]:
Load Distinct
right(FileBaseName(),3)&[COD_CONCEPTO] as [COD_CONCEPTO] ,
[INDICADOR_TIPO_CONCEPTO] ,
[CONCEPTO] ,
[COD_TIPO_CONCEPTO]
FROM [lib://Red.2.QVDExtr/V_DETALLES_COBRO_*.qvd] (qvd)
where Exists([COD_COBRO])

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

Nikos_Lamprinakos — Tue, 08 Jan 2019 11:15:02 GMT

Hi,

this is a change in Exists function behavior. From release notes:

The Exists function now requires the field_name to be loaded before its values are evaluated with the exists function. If the field_name you are evaluating is not yet loaded, you will receive a script error. The previous behavior was that the function returned neither TRUE nor FALSE, but NULL instead, so the result was dependent on the NULL handling of the surrounding context/expressions. Workaround: Use FieldNumber('field ') to check the existence of the field before you use the field in an Exists.

What this means is that the field name needs to be loaded (must be included in the Load) before its evaluated in 'Exists' function. Also, if it is renamed (e.g. Field as [Field 1]), Exists will also throw an error.

To work around this, you can load the field as is, and either transform using a preceding load, or if you are doing an optimized load from qvd, load the field twice to rename one of the instances, and use the other to filter. Then drop the field used for filtering.

Please let me know if that works for you, or there are better ideas out there! 🙂

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

marcus_sommer — Tue, 08 Jan 2019 12:22:44 GMT

I'm not sure if this change impact really many scenarios because it's quite common that the field for the exists-function exists already before this field in a exists-function is called - often a field/table is loaded and afterwards loadings are checking against it and there are also probably many cases in which such tables are specially created for it (at least I use it quite often and/or rename the needed fields). Here a simple example for it:

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

table: load * from QVD where exists(YEAR);

drop tables dummy;

Nevertheless I'm not sure if I think this change is good or bad.

- Marcus

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

luigi_reato — Tue, 08 Jan 2019 16:43:46 GMT

I had the same problem: a workaround is to write the field 2 times, as explained in the help

https://help.qlik.com/en-US/sense/November2018/Subsystems/Hub/Content/Sense_Hub/Scripting/InterRecordFunctions/Exists.htm

Exists(Employee, 'Bill')

Returns -1 (True) if the field value 'Bill' is found in the current content of the field Employee.

The statements Exists (Employee, Employee) and Exists (Employee) are equivalent.

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

Nikos_Lamprinakos — Thu, 10 Jan 2019 13:56:06 GMT

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

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

Nikos_Lamprinakos — Thu, 10 Jan 2019 13:57:33 GMT

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

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

marcus_sommer — Thu, 10 Jan 2019 14:45:58 GMT

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:

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

nsm1234567 — Fri, 18 Jan 2019 10:06:04 GMT

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!

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

sergio_loza — Wed, 20 Mar 2019 00:52:54 GMT

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.

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

nsm1234567 — Wed, 20 Mar 2019 09:20:02 GMT

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.

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

sergio_loza — Wed, 20 Mar 2019 16:26:29 GMT

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

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

nsm1234567 — Fri, 22 Mar 2019 13:08:51 GMT

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

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

stefanvb — Thu, 02 May 2019 08:04:24 GMT

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

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

EveLena — Wed, 05 Aug 2020 07:57:21 GMT

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.

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

nsm1234567 — Fri, 07 Aug 2020 08:10:51 GMT

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.