| 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.
|
| 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.
|
|
Employees:
LOAD * inline [
Employee|ID|Salary
Bill|001|20000
John|002|30000
Steve|003|35000
] (delimiter is '|');
Citizens:
Load * inline [
Name|Address
Bill|New York
Mary|London
Steve|Chicago
Lucy|Paris
John|Miami
] (delimiter is '|');
EmployeeAddresses:
Load Name as Employee, Address Resident Citizens where Exists (Employee, Name);
Drop Tables Employees, Citizens;
|
|
This results in a table called EmployeeAddresses in the data model, which can be viewed as a table visualization 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 |
|
|
|
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), means only the names from the table Citizens that are not in Employees are loaded into the new table.
|
| Employee |
Address |
| Mary |
London |
| Lucy |
Paris |
|
