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.
The statements Exists (Employee, Employee) and Exists (Employee) are equivalent.
|
Exists(Employee,
'Bill')
|
Returns -1 (True) if the field value 'Bill' is found in the current content of the field Employee.
|
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 that you can use in a table visualization using the dimensions Employee and Address.
The where clause: where Exists (Employee), means only the names from the tableCitizens that are also in Employees are loaded into the new table. The Drop statement removes the table Employees to avoid confusion.
Results
|
Employee |
Address |
Bill |
New York |
John |
Miami |
Steve |
Chicago |
|
|
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, 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.
Results
|
Employee |
Address |
Mary |
London |
Lucy |
Madrid |
|
Employees:
Load Employee As Name, ID, Salary;
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 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 |
|