Skip to main content
Announcements
Have questions about Qlik Connect? Join us live on April 10th, at 11 AM ET: SIGN UP NOW
cancel
Showing results for 
Search instead for 
Did you mean: 
datanibbler
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

1 Solution

Accepted Solutions
datanibbler
Champion
Champion
Author

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
sunny_talwar

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

marcus_sommer

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

hic
Former Employee
Former Employee

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

datanibbler
Champion
Champion
Author

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

giakoum
Partner - Master II
Partner - Master II

Uniqueness matters a lot :

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

The exists issue

datanibbler
Champion
Champion
Author

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

marcus_sommer

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

swuehl
MVP
MVP

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.

hic
Former Employee
Former Employee

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