Qlik Community

QlikView App Development

Discussion Board for collaboration related to QlikView App Development.

Announcements
QlikWorld, June 24-25, 2020. Free virtual event for DI and DA gurus. Register Today
cancel
Showing results for 
Search instead for 
Did you mean: 
Highlighted
Champion
Champion

EXISTS()


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

Tags (1)
1 Solution

Accepted Solutions
Highlighted
Champion
Champion

Re: EXISTS()

Thanks!

This is finally solved!

I have tried out another table - someone else told me I should be filtering in another table (I have that kind of filter already, but in the same table, apparently it is different) - I noticed that two values that I had actually filtered out were included if I did a JOIN with that table (with a perfect key in that new table, so that was ok).

=> Now I filtered in that new table as well and now     THE NR. OF RECORDS IS EXACTLY THE SAME!

I will close this thread now as I think the issue with EXISTS() is now kind of clarified, too.

With EXISTS(), there can be no issues with duplicates, only with NOT(EXISTS()), following the logic with the >previously loaded records<.

Thanks a lot once more!

Best regards,

DataNibbler

View solution in original post

27 Replies
Highlighted

Re: EXISTS()

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: EXISTS()

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

Highlighted

Re: EXISTS()

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

Highlighted
Champion
Champion

Re: EXISTS()

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

Highlighted
Partner
Partner

Re: EXISTS()

Uniqueness matters a lot :

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

The exists issue

Highlighted
Champion
Champion

Re: EXISTS()

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

Highlighted
MVP & Luminary
MVP & Luminary

Re: EXISTS()

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

Highlighted
MVP
MVP

Re: EXISTS()

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.

Highlighted

Re: EXISTS()

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