topic Re: Where Exists([MyField], 'MyValue') in QlikView

Where Exists([MyField], 'MyValue')

warfollowmy_ver — Sat, 16 Nov 2024 00:01:00 GMT

I have been using Where Exists() expression for a long time, but only now I decided to read the help and I was shocked.

The help says - expr -The value that you want to check if it exists. You can use an explicit value or an expression that refers to one or several fields in the current load statement.

All this time I could use explicit values in Where Exists()???

Why didn't I read this before?

Below is an example - Exists(Employee, 'Bill') - Returns -1 (True) if the field value 'Bill' is found in the current content of the field Employee.

Then I wrote the following code and it doesn't work and I don't understand why. What am I doing wrong?

t1:
NoConcatenate
LOAD RowNo() as [A] AutoGenerate(10);
STORE t1 into t1.qvd (qvd);
DROP Table t1;

t2:
NoConcatenate
LOAD [A] FROM [t1.qvd] (qvd) Where Exists([A], '2'); //Where Exists([A], 2) - value without quotes also doesn't work.

Re: Where Exists([MyField], 'MyValue')

alex00321 — Tue, 08 Sep 2020 01:19:41 GMT

The way I understand it is you would have a pre-stored table instead of QVD to use Exists. I updated your script, and you could find attached QVW to see if this is correct understanding. Thanks!

Re: Where Exists([MyField], 'MyValue')

warfollowmy_ver — Tue, 08 Sep 2020 03:34:13 GMT

Thanks for the answer.

What does your solution give? Only the value 2 remains in the table t2?

t1:
NoConcatenate
LOAD RowNo() as [A] AutoGenerate(10);

t2:
NoConcatenate
LOAD [A] as [B] Resident t1 Where Exists([A], '2');

The required selection does not take place, because in memory there is a table t1 with a field A and the selection goes through all values of the field A of all tables.

Re: Where Exists([MyField], 'MyValue')

Brett_Bleess — Tue, 22 Sep 2020 21:04:19 GMT

Only additional thing I have is a Design Blog post that may help, not sure though:

https://community.qlik.com/t5/Qlik-Design-Blog/Dual-Exists-Useful-Functions/ba-p/1465290

You will likely need to attach a sample QVW file otherwise so folks can dig into the data model as well as the expressions etc. in order to get further respones.

Regards,
Brett

Re: Where Exists([MyField], 'MyValue')

warfollowmy_ver — Wed, 23 Sep 2020 00:32:59 GMT

I presented a very specific code in the first message and a link to your own help.
You can create the file yourself and paste the code. I cannot even imagine a more specific formulation of the question.

Re: Where Exists([MyField], 'MyValue')

marcus_sommer — Wed, 23 Sep 2020 11:41:20 GMT

I never tried to use exists() in the way like your example, although the documentation says it's a valid statement and therefore I'm not sure if there is a bug respectively a change within the feature (since a certain release) or if it's a mistake within the documentation - means directly in parts a wrong statement or they missed better explaining examples.

I would say the important point within the documentation is:

You can use ~~an explicit value or~~ an expression that refers to one or several fields in the current load statement.

As far as such references are been used it worked like expected - but the mentioned explicit values seems not be treated as fieldvalues and are probably just ignored. In regard to your example you could change it in this way:

... Where Exists([A], match([A], 2)) ...

I doubt that this is much helpful as well as I don't see any benefits in using explicit values at all in exists() because match(), applymap() and similar functions are usually more suitable for such use-cases.

This doesn't mean that you should discard exists() with a second parameter at all else just ensuring that you refers to fields within the current load. In the past I used it quite often and it worked like it should. Nowadays the use is rather rarely because I create most of my qvd's with the appropriate fields and/or use specific load-orders with various temporary dummy-tables to avoid the second parameter and to be able to load the qvd's optimized.

- Marcus

Re: Where Exists([MyField], 'MyValue')

warfollowmy_ver — Wed, 23 Sep 2020 11:52:25 GMT

Thanks for the answer. Yes, that's what interests. I think the description help is just a mistake. But I was immediately delighted and thought how great it would be if I passed a list as values instead of loading a table with a field in advance, and this would work just as quickly and optimally as qvd optimized.

Re: Where Exists([MyField], 'MyValue')

marcus_sommer — Wed, 23 Sep 2020 13:38:15 GMT

As far as the second parameter is used it's no optimized load anymore. In the end it's more a matter of the own habits to create a workflow which ensured that the loadings could be run optimized as that it caused more efforts or leads to a confusing load-script. Like mentioned I use it quite frequently and sometimes dozens of times in a single script preferably with some generic data or external control-data (usually Excel in which the users could define which data should be considered), like:

ExistsFilter: load date(floor(yearstart(today())) + rowno() - 1) as DATE autogenerate daynumberofyear(today());
t: load * from QVD where exists(DATE);
drop table ExistsFilter;

ExistsFilter: load pick(recno(), 'Cat1', 'Cat5') as Cat autogenerate 2;
t: load * from QVD where exists(Cat);
drop table ExistsFilter;

- Marcus

Re: Where Exists([MyField], 'MyValue')

dadumas — Wed, 23 Sep 2020 18:28:23 GMT

Try this:

t1:
NoConcatenate
LOAD RowNo() as [B] AutoGenerate(10);
STORE t1 into t1.qvd (qvd);
DROP Table t1;

t2:
NoConcatenate
LOAD [A] FROM [t1.qvd] (qvd) Where Exists([B],[A]); //This will load where B=A

Re: Where Exists([MyField], 'MyValue')

rwunderlich — Fri, 21 Mar 2025 15:53:41 GMT

I know this is an old thread, but the problem is the:

Drop Table t1;

Field [A] no longer exists after the drop.

-Rob