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?

[AshutoshBhumkar]

Hi,
Could you share a sample scenario ?

[stefanvb]

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;

[lironbaram]

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 

[marcus_sommer]

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

[isorinrusu]

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]

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

[marcus_sommer]

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]

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]

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