topic Re: Exists in QlikView

Exists

NavinReddy — Wed, 29 Jan 2014 07:11:00 GMT

Hi All,

Could some one help me to know

use of Exists function and where can we implements

Thanks In Advance

Niranjan

Re: Exists

nilesh_gangurde — Wed, 29 Jan 2014 07:20:59 GMT

Hi,

This function is used when a value from previously loaded records of data is needed for the evaluation of the current record.

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.

-Nilesh

Re: Exists

Sokkorn — Wed, 29 Jan 2014 07:23:58 GMT

Hi Niranjan,

This one take from help file

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.

Here is example:

Table1:
LOAD * INLINE [
    F1, F2
    A, 1
    B, 2
    C, 3
    D, 4];

Table2
LOAD * INLINE [
    F3, F4
    A, AA
    B, BB
    C, CC
    E, EE];


Qualify *;

Table3:
LOAD
    *
RESIDENT Table2 WHERE Not Exists(F1, F3);

Result after load in Table3

F3,F4

E,EE

Regards,

Sokkorn