Does it exist?

In QlikView, we have the option to check if a value is already in a field using the "Exists" clause. I have seen some confusion with it over time, so hopefully this will help.

Exists is an inter-record function that we can use within the load script to check for the existence of a value in a previously loaded field.

The syntax is as follows:

exists(field [, expression])

The field is a field that already exists in our document - which includes any fields in the current load statement.

The expression is a calculation based on the data that we are currently loading.

Note that expression is not a required field. If it is excluded, exists calculates if the value of the field exists in any previous loaded value for that field.

Here is a simple example of using Exists to only load data that exists in a previously loaded field.


Budget:
Load * Inline [
User, Budget
Tom, 10000
Jane, 20000
May, 22000
Graham, 12300
];

Sales:
Load * Inline [
User, Sales
Tom, 11000
Jane, 19000
May, 21000
Graham, 13300
John, 23456
]
Where Exists(User);

The first load statement loads budget information for users Tom, Jane, May and Graham. The second load statement brings in the sales values for users but we state that we only want values for users already exist.

In this case, no values will be loaded for John.

Here, we didn't need to pass an expression (although we could have done Exists(User,User)) because we were looking for a value in the same field that was being loaded.

Here is another example of a similar sales loader:


Sales:
Load
RowNo() As row,
User,
Sales,
If(Exists(User,User), 1, 0) As Exists
;
Load * Inline [
User, Sales
John, 12222
Tom, 11000
Jane, 19000
May, 21000
Graham, 13300
John, 23456
];

If we now put a table of values for row, user and exists, we would see that John in Row 1 has a 0 for exists and in row 6 he has a 1. This shows us that Exists is working on data that is loading in the current load statement also, not just on data that has been loaded before.

The Expression parameter of the Exists statement is very useful - especially if you have complex keys in an Load.