Exists

[SteveZheng]

In definition, exists is defined as below:

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.

However, if I use this following script to verify, it will give me 0 value as True, why? Thanks!

load *,
exists(Name1,'Bill') as BillFlag;

load * inline [
Name1,age
Bill,46
John,9
Funny,5
];

result:

Name1             age         BillFlag

Bill                     46             0

John                    9            -1

Funny                 5             -1

[rwunderlich · Accepted solution]

You are using exists() in a preceding load.  The first time you test for 'Bill', 'Bill' has not actually been written out to Name1.

This would work as expected if you did this as two tables, that is with a resident load.

Data:
load * inline [
Name1,age
Bill,46
John,9
Funny,5
];

load *,
exists(Name1,'Bill') as BillFlag
Resident Data
;

[SteveZheng]

Hi, 

Thanks for your response. As suggested, 

I used two separate tables:

Data:
load * inline [
Name1,age
Bill,46
John,9
Funny,5
];

load *,
exists(Name1,'Bill') as BillFlag
Resident Data
;
drop table Data;

then result is like: (All BillFlag value became -1 now?)

Name1   Age   BillFlag

Bill     46    -1

John   9   -1

Funny   5  -1

[rwunderlich · Accepted solution]

That would be the expected result.  Every row is testing for the value 'Bill' in the field Name1, which would always be true. 

This is not a very useful example, but I suppose it illustrates the function working.  A more realistic use of Exists() would be:

Orders:
LOAD
  OrderId, CustomerId, ...
FROM Orders...

Customers:
LOAD CustomerId, CustomerName, ..
FROM Customers...
Where Exists (CustomerId, CustomerID)

That would test each CustomerId as we load it against all previously loaded values of CustomerId, ensuring that we only loaded Customers that exist in Orders. 

-Rob
http://masterssummit.com
http://qlikviewcookbook.com
http://www.easyqlik.com

[SteveZheng]

Thanks a lot for the explanation!
Steve