topic Re: Exists in QlikView

Exists

SteveZheng — Wed, 25 Nov 2020 16:16:04 GMT

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

Re: Exists

rwunderlich — Thu, 30 May 2019 14:21:29 GMT

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
;

Re: Exists

SteveZheng — Thu, 30 May 2019 19:39:01 GMT

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

Re: Exists

rwunderlich — Thu, 30 May 2019 22:44:22 GMT

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

Re: Exists

SteveZheng — Mon, 03 Jun 2019 15:11:11 GMT

Thanks a lot for the explanation!
Steve