Exists - script function

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

Syntax:

Exists(field_name [, expr])

Return data type: Boolean

Arguments:

Argument	Description
field_name	A name or a string expression evaluating to a field name to be searched for. The field must exist in the data loaded so far by the script.
expr	An expression evaluating to the field value to look for in the field specified in field-name. If omitted, the current record’s value in the specified field is assumed.

Example

Result

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

Data used in example:

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;

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!

Leave your feedback here