topic Re: Exists function choking... ? in QlikView

Exists function choking... ?

Anonymous — Mon, 22 Apr 2013 21:30:50 GMT

I'm having an issue with the Exists function.

In my load script, I'm trying to check to see if I've already processed a particular QVD file by comparing the filename with a filename field in a previously loaded table. However, Exists() is causing the script to exit with no error thrown, and nothing but a "general script error" in the logfile.

I've boiled it down to this:

Let vFileExistence = Exists(SliceFileName,'$(vFileShortName)');

In the debug window, this correctly expands to

Let vFileExistence = Exists(SliceFileName,'DataSlice00001.qvd')

(No semicolon shown in the debug window, but it is there in the code.)

The SliceFileName field does exist, and I've got DataSlice00001.qvd loaded in it via an inline load. But, the debug window shows vFileExistence <NULL> in red, and SliceFileName <NULL> in green.

Any ideas?

Thanks,

Kent

Re: Exists function choking... ?

Anonymous — Mon, 22 Apr 2013 21:41:32 GMT

Here's a very simple qvw example that exhibits the problem...

Re: Exists function choking... ?

hic — Mon, 22 Apr 2013 21:44:40 GMT

Is SliceFileName really a field?

If so, you should try with single quotes around it. Otherwise QlikView will assume that it is a variable. See more on http://community.qlik.com/blogs/qlikviewdesignblog/2013/04/09/quoteology

HIC

Re: Exists function choking... ?

Mon, 22 Apr 2013 21:48:01 GMT

assuming that SliceFileName is a valid field name in your script, enclose it within single quotes so that your statement looks like:

Let vFileExistence = Exists('SliceFileName','$(vFileShortName)');

Re: Exists function choking... ?

Mon, 22 Apr 2013 21:54:32 GMT

Check THIS:

MAY be you problem is: WHERE are you READING the QVD? Validate the Path where the "Exist" Variable that you are reading:

for example:

I had a problem because i was executing mi QVD with the Variable ExisteQVD and won't show Nothing because the path of the variable went to other place to find the QVD.

//=============== Cuenta los registros del archivo QVD en caso de existir =====================/

Let ExisteQVD = QvdNoOfRecords ('Dimensiones\ORDENES_PXS_TULSA_HIST.qvd');

//Si regresa -1 es que ExisteQVD es nulo y significa que el archivo QVD no se encuentra

Let ExisteQVD = IsNull(ExisteQVD);

IF ExisteQVD = -1 THEN

Store ordenes_pxs Into Dimensiones\ORDENES_PXS_TULSA_HIST.qvd;
Drop Table ordenes_pxs;

ELSE
Concatenate(ordenes_pxs)

LOAD

order_pxs,
serie_pxs,
project_px...

Re: Exists function choking... ?

Anonymous — Mon, 22 Apr 2013 21:59:28 GMT

Yes, SliceFileName is really a field...

And yes, putting the field name in single quotes worked. But that is NOT how the documentation specifies the Exists function should be used! (Page 326, QV11 SR1 documentation PDF)

QlikView really makes me want to SCREAM sometimes! C'mon guys, your reference manual really needs to be correct!

exists(field [ , expression ] )
Determines whether a specific field value exists in a specified field of the data loaded so far. Field is a name or a string expression evaluating to a field name. The field must exist in the data loaded so far by the script. Expr is an expression evaluating to the field value to look for in the specified field. If omitted, the current record’s value in the specified field will be assumed.
Examples:
exists(Month, 'Jan') returns -1 (true) if the field value 'Jan' is found in the current content of the field Month.
exists(IDnr, IDnr) returns -1 (true) if the value of the field IDnr in the current record already exists in any previously read record containing that field.
exists (IDnr) is identical to the previous example.
Load Employee, ID, Salary from Employees.csv;
Load FirstName& ' ' &LastName as Employee, Comment from Citizens.csv where exists (Employee, FirstName& ' ' &LastName);
Only comments regarding those citizens who are employees are read.
Load A, B, C, from Employees.csv where not exists (A);
This is equivalent to performing a distinct load on field A.

Re: Exists function choking... ?

hic — Tue, 23 Apr 2013 08:31:51 GMT

I don't see that the documentation contradicts the behaviour.

It's rather a question about how to quote things in QlikView: Unquoted strings (and references with double quotes or square brackets) are treated differently inside a Load compared to outside a Load. Inside a Load it is always a field reference; Ouside it is always a variable reference. And as I see it, it cannot be different. As a consequence, single quotes must be used for field references outside a load.

This is not a problem for the exists() function only. This is true for all functions. I agree that the documentation could be clearer on which quotes to use though.

HIC