topic Re: EXISTS() in QlikView

EXISTS()

datanibbler — Wed, 01 Jul 2015 12:36:11 GMT

Hi,

I have been here some times before with similar issues and I remember that this function is in all cases not as easy as the integrated help_file suggests. I remember for sure that it cannot be written with just one parameter as the help suggests. I think it only works if one or b oth of the key_fields in the tables are unique.

=> Well, that >or< is my issue. The scenario is this:

- In table_A (which I load earlier) that key_field (which I want to base the EXISTS() clause on) is unique.

<=> In table_B (which I load afterwards) I have a finer grain and that same key is not unique anymore.

=> Will that work if I have, in the LOAD for table_B, a clause >> WHERE EXISTS([keyfield_from_table_A], [keyfield_from_table_B]) <<?

Thanks a lot!

Best regards,

DataNibbler

Re: EXISTS()

sunny_talwar — Wed, 01 Jul 2015 12:39:12 GMT

As per my understanding, Uniqueness should not matter. Because you are trying to bring a list of keyfield_from_table_B which have a instance in keyfield_from_table_A. I have previously used this when both these fields were not unique (had duplicates) and it seemed to work, at least I hope it did

Best,

Sunny

Re: EXISTS()

marcus_sommer — Wed, 01 Jul 2015 12:41:28 GMT

Hi DataNibbler,

of course you could use exists() with only one parameter and table from the field isn't important - if you need to grab a field only from one table you need to duplicate this field or qualify them. The check will be always executed against the distinct field-values from your field.

- Marcus

Re: EXISTS()

hic — Wed, 01 Jul 2015 12:43:11 GMT

Maybe, Maybe not.

Lets say that you have

TableA:

Load ..., ID as Key From TableA;

TableB:

Load ..., ID as Key From TableB

Where Exists(Key,ID);

Then a comparison will be made for each record: Does the current value of "ID" exist in the field "Key"? This will work fine - initially. But if you use "not Exists()", you will get into trouble: Soon the "Key" field contains also values from TableB, and a duplicate in TableB will hence not be loaded. The comparison is not made against "Key" in table A. It is made against all previously loaded values - from both A and B.

You probably should try:

TableA:

Load ..., ID as Key, ID as KeyA From TableA;

TableB:

Load ..., ID as Key From TableB

Where Exists(KeyA,ID);

or you can try

TableA:

Load ..., ID as Key From TableA;

TableB:

Left Keep Load ..., ID as Key From TableB;

HIC

Re: EXISTS()

datanibbler — Wed, 01 Jul 2015 12:43:47 GMT

Hi Marcus,

that confuses me.

I guess that it will work then which is all I want for the moment.

Well, I guess I'll have to read up on that one once more and also read all the blogs and stuff on the issue.

Best regards,

DataNibbler

Re: EXISTS()

giakoum — Wed, 01 Jul 2015 12:44:04 GMT

Uniqueness matters a lot :

see this document for the solution. I think what you propose is ok :

The exists issue

Re: EXISTS()

datanibbler — Wed, 01 Jul 2015 12:46:59 GMT

So - it seems there exist() several opinions on what works and what doesn't regarding that function ... I will try.

Re: EXISTS()

marcus_sommer — Wed, 01 Jul 2015 12:56:02 GMT

Not really different opinions - we meant the same and have only described it in a different way. As usual hic had explained it an easier to understandable manner as me but there are more various options to use exists() so the ressource from giakoum is very useful.

- Marcus

Re: EXISTS()

swuehl — Wed, 01 Jul 2015 13:04:55 GMT

TableA:

Load ..., ID as Key From TableA;

TableB:

Load ..., ID as Key From TableB

Where Exists(Key,ID);

Then a comparison will be made for each record: Does the current value of "ID" exist in the field "Key"? This will work fine - initially. But soon, the "Key" field contains also values from TableB, and a duplicate in TableB will hence not be loaded. The comparison is not made against "Key" in table A. It is made against all previously loaded values - from both A and B.

Henric, maybe it's too hot in here, but I don't understand your conclusion.

Why should a duplicate of table B not be loaded?

I believe your comment is relevant for a WHERE NOT EXISTS() clause, though.

Re: EXISTS()

hic — Wed, 01 Jul 2015 13:08:29 GMT

I agree with Marcus. I don't think we disagree on what works and what doesn't.

On the issue of one parameter: Of course the Exists() function works fine also with just one parameter.

On the issue of uniqueness: It is as I see it not a matter of uniqueness. It is just that many think that the function makes a comparison with a field in a previous table, when it in fact makes a comparison with all loaded values of this field; also the ones from the current table. Hence, the second value of a duplicate in the same table will make Exists() return TRUE.

HIC

Re: EXISTS()

datanibbler — Wed, 01 Jul 2015 13:09:35 GMT

Okay.

the keyfield is calculated (or concatenated) from several elements, so from table_A (earlier), I can already name it whereas the one from table_B (second parameter to the EXISTS()) I have to write the formula as I cannot yet use the Alias_name. So they are different in a way.

Anyway, I will try loading the keyfield from table_A a second time with a different name and see what nr. of records I get then.

And I will try the alternative, using LEFT KEEP - but that might also prove troublesome because there is no common keyfield that is unique in both tables ...

Best regards,

DataNibbler

Re: EXISTS()

hic — Wed, 01 Jul 2015 13:15:57 GMT

No it is too hot in here... You are of course right.

HIC

Re: EXISTS()

datanibbler — Wed, 01 Jul 2015 13:16:36 GMT

Hi Henric,

I think this "many think" in your post means just what I have thought and others have stated - that EXISTS() (or NOT EXISTS()) does not work as expected when the keyfield is not unique in the current table (table_B, the one loaded later where that clause is applied).

The integrated help does not state (and I think many do not know) that EXISTS() checks the current LOAD also - as one of those that don't have your background_knowledge, that would match my definition of "does not work as is to be expected" then.

Well, let's see what I can make of this in practice.

Best regards,

DataNibbler

Re: EXISTS()

datanibbler — Wed, 01 Jul 2015 13:18:20 GMT

Hi both of you,

look forward to the weekend 😉 It's supposed to be 37° ...

Re: EXISTS()

swuehl — Wed, 01 Jul 2015 13:19:51 GMT

Up to 40°C forecasted

Re: EXISTS()

marcus_sommer — Wed, 01 Jul 2015 13:23:48 GMT

I think it worked as designed but I agree that the documentation to exists() didn't covered the complexity and confused the user and to learn it is a bit painful.

- Marcus

Re: EXISTS()

hic — Wed, 01 Jul 2015 13:30:40 GMT

I think the documentation is clear on that it is previously loaded records and not previously loaded tables:

"These functions are used when a value from previously loaded records of data is needed for the evaluation of the current record."

But I am aware that there are misunderstandings in this area.

HIC

Re: EXISTS()

datanibbler — Wed, 01 Jul 2015 13:43:07 GMT

Yep. I cannot argue further 😉

I have tried two ways now:

- I have to put together my table_B (the one loaded later) from three elements, thus I have three LOADs.

- At first I had an EXISTS() clause in every single one

- Now I have tried loading them all first and then using the EXISTS() clause - the nr. of records is exactly the same.
From all I know, there should be the same nr. of records in both tables, with all the right filters applied, but I end up
with fewer records in table_B - about 10.000 less, so that might be the duplicate thing (the field is not unique in
table_B as that has a finer grain than table_A, so I cannot create a key that would be both common to those two
tables AND unique in table_B ...

Now I will try the LEFT KEEP() option.

P.S.: I have also renamed the field in table_A so that my clause read like

>> ... WHERE EXISTS([key_v2], [key]) <<

Re: EXISTS()

datanibbler — Wed, 01 Jul 2015 13:55:44 GMT

Hmmm ... the LEFT KEEP (with the table_name specified in brackets) produces the exact same nr. of records than the two options before - so I guess that must be correct, even if I don't wholly understand why there are fewer records than in my table_A ... but in companies such as this one, one doesn't have to understand everything ...

Many thanks to all!

Best regards,

DataNibbler

P.S.: OK, this is halfway plausible - it could be that the data in different tables is moved into an archive in different intervals. I will limit my time_filter on table_A to the last three months and see. If that assumption is correct, then the difference in the nr. of records should decrease.

Re: EXISTS()

datanibbler — Wed, 01 Jul 2015 14:52:30 GMT

This is most strange ...

I have now done all the loading without the final LEFT KEEP (or EXISTS()) and written down four or five fields that I have found (using a straight_table) to not be unique in my table_B.

<=> Then I have activated that LEFT KEEP again - and now I can find NONE of those obviously duplicated values! How can that be?

I understand that the effect of using EXISTS() can be to return too few records because of all the duplicate values, only the first one is kept - but how can it remove ALL of the duplicated values (incl. the original)??

Can you shed some light on this?

Thanks a lot!

Best regards,

DataNibbler