Skip to main content

Exists - script function

Exists() determines whether a specific field value has already been loaded into the field in the load script. The function returns TRUE or FALSE, so can be used in the where clause of a LOAD statement or an IF function.

Information noteYou can also use Not Exists() to determine if a field value has not been loaded, but caution is recommended if you use Not Exists() in a where clause. The Exists() function tests both previously loaded tables and previously loaded values in the current table. So, only the first occurrence will be loaded. When the second occurrence is encountered, the value is already loaded. See the examples for more information.

Syntax:  

Exists(field_name [, expr] )

Return data type: Boolean

Arguments:  

Exists arguments
Argument Description
field_name

The name of the field where you want to search for a value. You can use an explicit field name without quotes.

The field must already be loaded by the script. That means, you cannot refer to a field that is loaded in a clause further down in the script.

expr

The value that you want to check if it exists. You can use an explicit value or an expression that refers to one or several fields in the current load statement.

Information noteYou cannot refer to fields that are not included in the current load statement.

This argument is optional. If you omit it, the function will check if the value of field_name in the current record already exists.

Example 1:  

Exists (Employee)

Returns -1 (True) if the value of the field Employee in the current record already exists in any previously read record containing that field.

Example 2:  

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.

Example 3:  

Employees: LOAD * inline [ Employee|ID|Salary Bill|001|20000 John|002|30000 Steve|003|35000 ] (delimiter is '|');   Citizens: Load * inline [ Employee|Address Bill|New York Mary|London Steve|Chicago Lucy|Madrid Lucy|Paris John|Miami ] (delimiter is '|') where Exists (Employee); Drop Tables Employees;

 

This results in a table called Citizens in the data model, which can be viewed as a table chart using the dimensions Employee and Address.

The where clause: where Exists (Employee), means only the names from the table Citizens that are also in Employees are loaded into the new table. The Drop statement removes the temporary table Employees to avoid confusion.

Example 3 results
Employee Address
Bill New York
John Miami
Steve Chicago

Example 4:  

Employees: Load * inline [ Employee|ID|Salary Bill|001|20000 John|002|30000 Steve|003|35000 ] (delimiter is '|');   Citizens: Load * inline [ Employee|Address Bill|New York Mary|London Steve|Chicago Lucy|Madrid Lucy|Paris John|Miami ] (delimiter is '|') where not Exists (Employee);   Drop Tables Employees;

 

The where clause includes not: where not Exists (Employee).

This means that only the names from the table Citizens that are not in Employees are loaded into the new table.

Note that there are two values for Lucy in the Citizens table, but only one is included in the result table. When you load the first row with the value Lucy, it is included in the Employee field. Hence, when the second line is checked, the value already exists.

Example 4 results
Employee Address
Mary London
Lucy Madrid

Example 5:  

This example shows how to load all values.

Employees: Load Employee As Name; LOAD * inline [ Employee|ID|Salary Bill|001|20000 John|002|30000 Steve|003|35000 ] (delimiter is '|');   Citizens: Load * inline [ Employee|Address Bill|New York Mary|London Steve|Chicago Lucy|Madrid Lucy|Paris John|Miami ] (delimiter is '|') where not Exists (Name, Employee);   Drop Tables Employees;

 

To be able to get all values for Lucy, two things were changed:

  • A preceding load to the Employees table was inserted where Employee was renamed to Name.

    Load Employee As Name;

  • The Where condition in Citizens was changed to:

    not Exists (Name, Employee).

This creates fields for Name and Employee. When the second row with Lucy is checked, it still does not exist in Name.

Example 5 results
Employee Address
Mary London
Lucy Madrid
Lucy Paris

Data used in example:

LOAD * inline [ Employee|ID|Salary Bill|001|20000 John|002|30000 Steve|003|35000 ] (delimiter is '|');   Citizens: Load * inline [ Employee|Address Bill|New York Mary|London Steve|Chicago Lucy|Madrid Lucy|Paris John|Miami ] (delimiter is '|');

Did this page help you?

If you find any issues with this page or its content – a typo, a missing step, or a technical error – let us know how we can improve!

Join the Analytics Modernization Program

Remove banner from view

Modernize without compromising your valuable QlikView apps with the Analytics Modernization Program. Click here for more information or reach out: ampquestions@qlik.com