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.
Syntax:
Exists(field_name [, expr] )
Return data type: Boolean
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:
This results in a table called EmployeeAddresses in the data model, which can be viewed as a table chart using the dimensions Employee and Address.
The where clause: where Exists (Employee, Name), means only the names from the tableCitizens that are also in Employees are loaded into the new table. The Drop statement removes the temporary tables Employees and Citizens to avoid confusion.
| Employee | Address |
|---|---|
| Bill | New York |
| John | Miami |
| Steve | Chicago |
Example 4:
Replacing the statement in the sample data in the previous example that builds the table EmployeeAddresses with the following, using where not Exists.
NonEmployee:
Load Name as Employee, Address Resident Citizens where not Exists (Employee, Name);
The where clause includes not: where not Exists (Employee, Name).
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 the value is included in the Employee symbol table. Hence, when the second line is checked, the value now exists.
The next example shows how to load all values.
| Employee | Address |
|---|---|
| Mary | London |
| Lucy | Madrid |
Example 5:
To be able to get all values for Lucy you need to change two things:
-
Add a preceding load to Employees where you rename Employee to Name.
Load Employee As Name, ID, Salary;
-
Change the Where condition in Citizens to:
not Exists (Name, Employee).
This will create different symbol tables for Name and Employee. When the second row for Lucy is checked, it still does not exist in Name.
| Employee | Address |
|---|---|
| Mary | London |
| Lucy | Madrid |
| Lucy | Paris |
Data used in example:
