Qlik Community

Qlik Sense App Development

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

Highlighted
Partner
Partner

Where exists function giving error in Qlik Sense Nov 2018 release

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?

 

 

17 Replies
Partner
Partner

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

Hi,
Could you share a sample scenario ?
Partner
Partner

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

 

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;

Partner
Partner

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

hi 

i think i encountered this also 

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

MVP & Luminary
MVP & Luminary

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

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

Partner
Partner

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

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

Nikos_Lamprinakos
New Contributor

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

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! 🙂

 

MVP & Luminary
MVP & Luminary

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

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

luigi_reato
New Contributor

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

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/InterRecor...

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.

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